1. Introducing MySQLdb!
In this tutorial, we will query a MySQL database for a value which we take from user input. To do this, we use a class to represent the table of the database and a loop to iterate through the table.
Perhaps the easiest Python module to use with MySQL is MySQLdb. It is available from Sourceforge. The main documentation on this module may also be found at Sourceforge here.
2. Importing MYSQLdb For Use in Your Program
Once you have installed the module using the instructions found here, you can import it like any other module:
import MySQLdb
3. A Class Called Table
To access a database, it is best to define a class. Here I define a class called Table which allows me to retrieve data (__getitem__) and to know the number of records (__len__).
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def __getitem__(self, item):
self.dbc.execute("select * from %s limit %s, 1" %(self.name, item))
return self.dbc.fetchone()
def __len__(self):
self.dbc.execute("select count(*) from %s" % (self.name))
l = int(self.dbc.fetchone()[0])
return l 4. Defining an Instance of Table
Next, we need to define the database and create an instance of Table.
db = MySQLdb.connect( db="testable")
records = Table(db, "test") Here the database name is 'testable'. The table from 'testable' which we will query is 'test'. It may be stating the obvious, but before executing the program, you must ensure that you actually have access to a database and table by these names (or whatever names you insert into the program). As usual, one can tweak these lines so that the values are derived from the command line by using sys.argv[1] and sys.argv[2], respectively.
We shall instead take the value of sys.argv[1] as the value for the query. So, import the sys module by adding ", sys" to the line where we imported MySQLdb. If you are doing this in the Python shell, simply type:
>>> import sys 5. Querying the Database
Finally, we need to define a main function in which we query the database. The main() function will include a simple loop for iterating through the rows of the table as follows:
def main():
for c in xrange(len(records)):
fvalues = records[c]
print fvalues
if fvalues[0] == sys.argv[1]:
print fvalues[1] Here we set up a for loop which runs from 1 (understood) to whatever the length of the table is. For each row, the entire row is read into 'fvalues'. If the first value in the row is equal to the string the program received from the user, the record is printed.
6. Where To Go From Here
In this way, one can quickly query the key value of a MySQL table. Of course, if we changed the superscript of 'fvalues' to 1 instead of 0, the program would check the second field, and so on. A fuller implementation than this example might also allow the user to define the row of the table. On a simpler level, if one simply wants to read the entire table out, one simply need remove the if loop and to unindent the print command to the appopriate level.
Needless to say, this only scratches the surface of what you can do with MySQLdb. Future tutorials on this module are planned. But if you cannot wait and want to dig in, see the MySQLdb User's Guide here .