The simplest way to insert rows into a table is to use a non-parameterized INSERT statement, meaning that values are specified as part of the SQL statement. A new statement is constructed and executed for each new row. As in the previous example, a cursor is required to execute SQL statements.
The following sample program inserts two new customers into the sample database. Before disconnecting, it commits the transactions to the database.
import sqlanydb # Create a connection object, then use it to create a cursor con = sqlanydb.connect( userid="DBA", pwd="sql" ) cursor = con.cursor() cursor.execute("DELETE FROM Customers WHERE ID > 800") rows = ((801,'Alex','Alt','5 Blue Ave','New York','NY', 'USA','10012','5185553434','BXM'), (802,'Zach','Zed','82 Fair St','New York','NY', 'USA','10033','5185552234','Zap')) # Set up a SQL INSERT parms = ("'%s'," * len(rows[0]))[:-1] sql = "INSERT INTO Customers VALUES (%s)" % (parms) print sql % rows[0] cursor.execute(sql % rows[0]) print sql % rows[1] cursor.execute(sql % rows[1]) cursor.close() con.commit() con.close() |
An alternate technique is to use a parameterized INSERT statement, meaning that question marks are used as place holders for values. The executemany method is used to execute an INSERT statement for each member of the set of rows. The new row values are supplied as a single argument to the executemany method.
import sqlanydb # Create a connection object, then use it to create a cursor con = sqlanydb.connect( userid="DBA", pwd="sql" ) cursor = con.cursor() cursor.execute("DELETE FROM Customers WHERE ID > 800") rows = ((801,'Alex','Alt','5 Blue Ave','New York','NY', 'USA','10012','5185553434','BXM'), (802,'Zach','Zed','82 Fair St','New York','NY', 'USA','10033','5185552234','Zap')) # Set up a parameterized SQL INSERT parms = ("?," * len(rows[0]))[:-1] sql = "INSERT INTO Customers VALUES (%s)" % (parms) print sql cursor.executemany(sql, rows) cursor.close() con.commit() con.close() |
Although both examples may appear to be equally suitable techniques for inserting row data into a table, the latter example is superior for a couple of reasons. If the data values are obtained by prompts for input, then the first example is susceptible to injection of rogue data including SQL statements. In the first example, the execute method is called for each row to be inserted into the table. In the second example, the executemany method is called only once to insert all the rows into the table.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |