Database access using Python

[debtboy]

Lately I've been writing Python scripts in an effort to better
understand the language. After completing this last one, I
decided to make a little tutorial out of it.

This script uses the dbapi2 of the pysqlite2 package, which is
basically just a wrapper around a sqlite database which simplifies
Python access.
A sqlite database is created and a fixed set of data is inserted into it,
then a query is executed with the result being printed to standard out.

Here is the complete script:

#!/bin/env python

from pysqlite2 import dbapi2

debtboy_connection = dbapi2.connect('linux.db')
debtboy_cursor = debtboy_connection.cursor()

debtboy_cursor.execute('''CREATE TABLE
                          release(distro VARCHAR(15),
                          version VARCHAR(5),
                          type VARCHAR(10))''')

debtboy_data = [
                 ('Gentoo', '2009', 'rolling'),
                 ('Arch', '2009', 'rolling'),
                 ('Slackware', '13.1', 'fixed'),
                 ('Fedora', '11.0', 'fixed'),
                 ('Debian', '5.0', 'fixed'),
                 ('Ubuntu', '9.04', 'fixed'),
                 ('openSUSE', '11.1', 'fixed'),
                 ('Mandriva', '2009', 'fixed'),
                 ('Mint', '7.0', 'fixed'),
                 ('PCLinuxOS', '2009', 'rolling'),
               ]
 
debtboy_cursor.executemany('''INSERT INTO 
                              release(distro, 
                              version,type) 
                              VALUES(?, ?, ?)''', debtboy_data)

debtboy_cursor.execute('''SELECT distro, 
                          version, 
                          type 
                          FROM release
                          ORDER BY distro''')

print 

column_width = 15

for column_description in debtboy_cursor.description:
   print column_description[0].ljust(column_width) ,
print

print '-' * 45

debtboy_index = range(len(debtboy_cursor.description))

for row in debtboy_cursor:
   for index in debtboy_index:
      debtboy_value = str(row[index])
      print debtboy_value.ljust(column_width) ,
   print

debtboy_connection.commit()
debtboy_connection.close()
print

This is the first section, it starts off with a she-bang #!
and following that is the Python interpreter.
Then the dbapi2 is imported from pysqlite2 so we can establish
a database connection (if the database were trying to connect
doesn't exist, then it will be created).
A db cursor is also created and used to make a table.

#!/bin/env python

from pysqlite2 import dbapi2

debtboy_connection = dbapi2.connect('linux.db')
debtboy_cursor = debtboy_connection.cursor()

debtboy_cursor.execute('''CREATE TABLE
                          release(distro VARCHAR(15),
                          version VARCHAR(5),
                          type VARCHAR(10))''')

Here is the fixed set of data (Linux distro names, versions and types).
3 string literals are each saved in a tuple and all the tuples are saved
in a list. This data is inserted into the database using the executemany
function which saves me from looping and inserting each row.

debtboy_data = [
                 ('Gentoo', '2009', 'rolling'),
                 ('Arch', '2009', 'rolling'),
                 ('Slackware', '13.1', 'fixed'),
                 ('Fedora', '11.0', 'fixed'),
                 ('Debian', '5.0', 'fixed'),
                 ('Ubuntu', '9.04', 'fixed'),
                 ('openSUSE', '11.1', 'fixed'),
                 ('Mandriva', '2009', 'fixed'),
                 ('Mint', '7.0', 'fixed'),
                 ('PCLinuxOS', '2009', 'rolling'),
               ]
 
debtboy_cursor.executemany('''INSERT INTO 
                              release(distro, 
                              version,type) 
                              VALUES(?, ?, ?)''', debtboy_data)

This last section executes a query selecting the data, which is
then looped through and displayed on standard out. You probably
noticed a number of stand alone "print" commands which were used
to format the data (adding a newline).

debtboy_cursor.execute('''SELECT distro, 
                          version, 
                          type 
                          FROM release
                          ORDER BY distro''')

print 

column_width = 15

for column_description in debtboy_cursor.description:
   print column_description[0].ljust(column_width) ,
print

print '-' * 45

debtboy_index = range(len(debtboy_cursor.description))

for row in debtboy_cursor:
   for index in debtboy_index:
      debtboy_value = str(row[index])
      print debtboy_value.ljust(column_width) ,
   print

debtboy_connection.commit()
debtboy_connection.close()
print

I chose to make this an executable script from the shell, so
here I am using chmod to make it executable.
I could have left it as a regular file and ran it from the Python
interpreter in which case the first line wouldn't be needed.



Here is the standard out result of the above query.


A simple script which demonstrates database access using Python.

[Jordan]

Very well done! +rep

[WingedPanther]

Nice demo