1. Python and MySQL: Getting Started With Python and MySQL
In this tutorial, we will insert into a MySQL database some value that we take from a user's input. As with reading out of the database,we again will use a class to represent the table of the database.
2. Getting MySQLdb
Perhaps the easiest Python module to use with MySQL is MySQLdb. In case you have not gone through the tutorial on reading from a MySQL database, MySQLdb is available from Sourceforge. The main documentation on this module may also be found at Sourceforge here.
3. Importing MySQLdb
Once you have installed the module using the instructions found at SourceForge, you can import it like any other module:
import MySQLdb
4. Taking Data From the Command Line
For this tutorial, we will be taking the data from the command line. To do this, we will use the sys module, so add to the same line ", sys". The import line should now read as follows:
import MySQLdb, sys >>> import sys Then define two variables, id and input, according to the sys.argv[] arguments:
id = sys.argv[1]
input = sys.argv[2] 5. A Touch of Class
As I said earlier, it is best to define a class in order to access a database. Here I again define a class called Table which allows me to insert data (additem). Unlike the example code for reading from the database, we here do not need to know the length of the database to read through all of its records.
The following code presupposes a table with two columns in it: the first field is the record identifier and the second is the record content.
class Table:
def __init__(self, db, name):
self.db = db
self.name = name
self.dbc = self.db.cursor()
def additem(self, item):
sql = "INSERT INTO " + self.name + " VALUES(" + id + ", " + item + ")"
self.dbc.execute(sql)
return
Rather than define the statement within the argument of self.dbc.execute, I here define it and then call it as an argument. If you have trouble with your script and it is giving unexpected output, try to define the variables separately from the arguments and then print them out. This will give you insight into the flow of the program and enable you to see where things are going awry.
6. The main() Thing
Next, in the definition of the main() function, we need to define the database and create an instance of Table.
def main():
db = MySQLdb.connect( db="testable")
table = Table(db, "test")
table.additem()
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.
7. Calling the main() Function
Finally, we need to call the main function. In keeping the main activity of the program within a function, the program remains compartmentalized and maintainable. So type the following to finish the program:
if __name__ == '__main__':
main() 8. Executing the Program
With this class and function, one can easily add to MySQL tables on the fly. As it stands, the program expects input like this when it is executed:
>python insertmysql.py <id> <data> If the program allowed for the database, user, and table to be defined at run-time (i.e., when the program is called), you then have a very flexible data insertion program that you can call from other programs.While reading and writing is about all most people do with databases, if you would like to read more about MySQLdb, see the MySQLdb User's Guide at [link url=http://sourceforge.net/docman/display_doc.php?docid=32071&group_id=22307] SourceForge .