Welcome to Serpia's blog!

Blog Entry

Using MySQL in Python

MySQL is a very popular open source  database, it's used in in millions and millions of (web)applications. There is a module for Python that can interface with a MySQL database server, you can obtain it here. The database can be installed on your own machine (local), on a server or on a webserver. If you want to use MySQL for your website, you should make sure that your webhost supports MySQL (very likely) and that they will provide you with the necessary instructions on how to manage it. You will also need the Python CGI module. CGI stands for Common Gateway Interface and it allows you to pass your Python instructions (e.g. using a webform) to a program that is located on the webserver and display the results on a webpage. For this little tutorial we will use a local database. Check out the Search engine tutorial on this website here for more information on how to use MySQL.

Step one: create a MySQL database

I will not explain how to install MySQL and how to create a database, there are many excellent tutorials on the Internet about MySQL you can refer to.

Step two: creating getdata.py

I hope you have succeeded in installing MySQL, creating a database and setting up permissions for your database. import MySQLdb The MySQLdb module is mandatory of course, it is there to make a connection between Python and MySQL. class Eb_db: def __init__(self): try: connection = MySQLdb.connect(host="localhost", user="dim", passwd="12opy7", db="serpiadb" ) cursor = connection.cursor() cursor.execute( "SELECT * FROM Table1 " ) First, we will try to make a connection to our MySQL database. To do this we need to know where the database is located, who the user and what the password is, and the name of the database. In out particular example the database is located on a local machine (localhost) but it might as well be located on the Internet (host would be "www.serpia.com" for instance).
The next step is create a variable named cursor, and assign the value connection.cursor() to it. We need this cursor to execute SQL commands. The next line is an example of such a command, cursor.execute executes the SQL command SELECT * FROM Table1. In order words, select all records from a table named Table1 from the database serpiadb. except MySQLdb.OperationalError, message: errorMessage = "Error %d:\n%s" % (message[ 0 ], message[ 1 ] ) return Now, when try does not succeed, we will create an exception, named MySQLdb.OperationalError. No big deal though, MySQLdb.OperationalError is a part of the MySQLdb module and the only thing we have to do is to output the message to the user! This is just how it works, don't worry about it. You should understand the try...except..else... mechanism however. else: self.data = cursor.fetchall()    self.fields = cursor.description    cursor.close()   connection.close() This is where some things are actually happening. We will create the variables, data and fields. To the first variable, data, we will assign the result of cursor.fetchall(). The fetchall command will create a tuple from the SELECT * FROM Table1 command. Now, what is a tuple? A tuple is a sequence of items and can be used as a key-value pair. The latter makes a tuple very useful for database applications.To the second variable, fields, we will assign the result of cursor.description(). This will result in a list of the fields of our database. The last two lines will see to it that the database is closed and disconnected.

Comments

Post a comment

Your name:

Comment: