Executing MySQL Queries in Python

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.

Example 1: Create Table


In this example you can see how a basic query without any parameters is executed.

Example 2: Execute Insert / Single Substitution Query

		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.

Example 3: Multiple Substitution Query

		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.

Example 4: Execute Select

		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.

<-- Home -->