Custom queries III: MySQL

Ok, we saw how to connect to an SQLite database in Python. Now let's see MySQL. There's only a small difference in syntax, but it's significant. The steps, however, are the same.

Import the library
import MySQLdb as ms

Note: The as specifies an alternate name for the library. In this case, I won't have to type MySQLdb again and again. I'll just type ms.

Establish a connection
conn = ms.connect (host, username, password, database_name)

If you're running this on a machine, normally host is localhost. You'll have to create a root user, which you'll have to see how to do in your particular OS on the Internet. Provide that username and password. Then execute the command create database db_name and use this database as the fourth field.

Exhausted? So am I. I know it's a lot of effort, but trust me, the results are totally worth it.

Create a cursor as usual
cur = conn.cursor ()

Remember I said %s doesn't work in SQLite? Well, that's the only thing that works here. Let's write a query.

query = "select name from employee where empid = %s" % empid

READ THIS VERY CAREFULLY:
Now, empid is an integer, but you still have to pass is as a string. Assume empid is 1234. The query then becomes:
select name from employee where empid = 1234

Let's look at a query where we need to pass a string.
query = "select empid from employee where name = '%s'" % name

Notice the difference? %s from the first query is replace by '%s' in the second. Assume name is Douglas. The query now becomes:
select empid from employee where name = 'Douglas'

Don't worry if you're confused, go through the paragraph once again. Let it sink in.

OK, NOW RELAX AND ENJOY THE REMAINING PART.

Ok, the rest is pretty much the same.

cur.execute (query)
for row in cur.fetchall ():
    print row[0]

for row in cur: in SQLite is replaced by cur.fetchall (): in MySQL.

Alternatively, you can also use cur.fetchone () if you're sure only one row is going to be returned (for primary keys) or just want it that way.

Hope I didn't miss anything. If I did, please comment. The remaining things are the same for both SQLite and MySQL. Don't forget to commit and close the connection once the job is done.
Cheers!