7. Using Python MySQL API at CSC

The Python MySQL API is available in Taito at CSC, but it is not officially supported. Login to taito.csc.fi and write a Python script that access to the database server.

The following tasks are usually performed by a Python database script:

  • MySQLdb module is imported
  • The connection to the MySQL server is opened
  • The statements are executed and their results sets are retrieved
  • The server connection is closed

The following guidance assumes that you have a database user account to the database service at CSC. If you are accessing another MySQL server replace the server name (kaivos.csc.fi) in script the server name you are using.

Write a MYSQL database access script

Use your favourite text editor to create a named script file e.g. mydb_script.py. Then copy the following text to the script.

# mydb_script.py script to show MySQL server version

    import sys
    import MySQLdb

    try:
        conn = MySQLdb.connect (host = "kaivos.csc.fi",
                                user = "your_database_user_account",
                                passwd = "your_database_password",
                                db = "your_database_name")
    except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

    try:
        cursor = conn.cursor ()
        cursor.execute ("SELECT VERSION()")
        row = cursor.fetchone ()
        print "server version:", row[0]
        cursor.close ()
    except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

    conn.close ()
First of all the script imports modules sys and MySQLdb. The module MySQLdb is a driver to the MySQL database, and it must be imported before connection to database can be created. The sys is needed for error handling by using the try statement and an except clause that contains the error-handling code. The MySQLdb.Error exception class stores the error information to the variable e. If an exception occurs, the error information is printed from e.args as a two-element tuple. The first value is numeric error code and the second value is a string describing the error.

If an exception does not occur, the connection to the database is established by invoking the connect() method with the connection parameters. These parameters are the database server, database user account, database password and database use want to use. Replace "user", "passwd" and "db" values corresponding your database user account, database password and database.

The connect() method returns a connection object, which invokes cursor() method to create cursor object. The execute() method of cursor object sends SQL statement to the database server and fetchone() method receives results of the SQL statement. The "SELECT VERSION()"statement contains a single value as a tuple, which the script prints. Finally the cursor and connection are closed by close() method.

Running the MYSQL database access script

Run the script from the command line with Python interpreter:

python mydb_script.py

 

or add following to the beginning of the script:

#!/appl/opt/python/2.7.3-gcc-shared/bin/python

 

Then make the script executable and run it directly:

chmod +x mydb_script.py
./mydb_script.py

 

The statements issuing methods

The execute() method is for issuing SQL statements e.g.

cursor.execute ("DROP TABLE IF EXISTS table_name")
   cursor.execute ("""
       CREATE TABLE table_name
       (
         col1_name   col1_type,
         col2_name   col2_type
       )
     """)

 

If your SQL statement invoked with execute() method returns a result set, you must retrieve the result set with different methods. The method fetchone() is used to get the rows one at a time, and the method fetchall() is used to get rows all at once.

The result set retrieval using fetchone()

     cursor.execute ("SELECT col1, col2 FROM table")
     while (1):
       row = cursor.fetchone ()
       if row == None:
         break
       print "%s, %s" % (row[0], row[1])

 

The result set retrieval using fetchall()

     cursor.execute ("SELECT col1, col2 FROM table")
     rows = cursor.fetchall ()
     for row in rows:
       print "%s, %s" % (row[0], row[1])

 

The rows can be fetched as dictionaries, but dictionary access requires a different kind of cursor. When using dictionaries you can access the column values by names.

cursor = conn.cursor (MySQLdb.cursors.DictCursor)
     cursor.execute ("SELECT col1, col2 FROM table")
     result_set = cursor.fetchall ()
     for row in result_set:
       print "%s, %s" % (row["col1"], row["col2"])

 

Transactions

If you are using the default MyISAM storage engine, you do not need to worry about transactions, because MyISAM is a non-transactional storage engine. However, if you are using transactional storage engine like InnoDB, commit your data changes with commit () method. Commit updates after cursor closing but before closing connection to the database.

cursor.close ()
conn.commit ()
conn.close ()

 

Use your favourite text editor to create a named script file e.g. mydb_script.py. Then copy the following text to the script.

 

  Previous chapter     One level up     Next chapter