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="<user_id>", pwd="<password>" )
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 placeholders 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="<user_id>", pwd="<password>" )
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.