Python is a programming language that lets you work more quickly and integrate your systems more effectively.
You can edit site.cfg if necessary for your environment, but it is not required.
$ python setup.py build
$ sudo python setup.py install (or su first)
Import the library in your code
Now, all you need to do is “import MySQLdb” and open a connection:
This very simply Python script written by Paul DuBois (email@example.com) can be used as an example to open a MySQL connection and print the server’s version:
conn = MySQLdb.connect (host = “localhost”,
user = “testuser”,
passwd = “testpass”,
db = “test”)
cursor = conn.cursor ()
cursor.execute (“SELECT VERSION()”)
row = cursor.fetchone ()
print “server version:”, row
If you wanted to add in error handling for failures, you could wrap the connection
in a try: statement and then do something like this:
except MySQLdb.Error, e:
print “Error %d: %s” % (e.args, e.args)
Query execution examples
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
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.
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 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.
This is too complicated for me
Well, maybe you shouldn’t be coding in Python then. If you want the easy way out, install the latest version of Python which comes with MySQL support out of the box.
Share on FacebookTweetOriginal article: Writing Python code to use MySQL©2013 e r i k i m h d o t c o m. All Rights Reserved.
No related posts.