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.
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.
Once you have installed the module using the instructions found at SourceForge, you can import it like any other module:
If this looks foreign to you, consider revisiting importing modules via my tutorial on Beginning Python.
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:
If you are doing this in the Python shell, simply type:
import MySQLdb, sys
Python will not be bothered by the late addition.
>>> import sys
Then define two variables, id and input, according to the sys.argv arguments:
This tells Python that the first argument after the program's name should be assigned to the variable 'id'. Likewise, the second argument gets assigned to 'input'.
id = sys.argv input = sys.argv
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.
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 and sys.argv, respectively.
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()
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 .