Executing queries is very simple in MySQL Python. All you need to do is take your cursor object and call the 'execute' function. The execute function requires one parameter, the query. If the query contains any substitutions then a second parameter, a tuple, containing the values to substitute must be given.
cur.execute("CREATE TABLE song ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, title TEXT NOT NULL )")
In this example you can see how a basic query without any parameters is executed.
songs = ('Purple Haze', 'All Along the Watch Tower', 'Foxy Lady')
for song in songs:
cur.execute("INSERT INTO song (title) VALUES (%s)", song)
print "Auto Increment ID: %s" % cur.lastrowid
In this example, you can see how a query is executed with parameters and you can see how to get the id generated from an auto increment column.
cur.execute("SELECT * FROM song WHERE id = %s or id = %s", (1,2))
It is important to note that when there are multiple parameters to substitue, you must use a tuple to enclose all of the parameters that need to be passed. The parameters are then substituted from left to right with tupe[0] being the left most substitution and tuple[n] being the right most substitution.
numrows = cur.execute("SELECT * FROM song")
print "Selected %s rows" % numrows
print "Selected %s rows" % cur.rowcount
From this you can see that executing select queries is very easy. There are two ways you can get the number of rows the query returned. The MySQLdb specific way is to save the return value from the execute statement. This is NOT the preferred way. You should use the second method which is the Python DB-API 2.0 way because it will make it easier if you ever have to change databases. Both method's are illustrated in this example.