Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Database access using Python


  • Please log in to reply
2 replies to this topic

#1 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 19 September 2009 - 06:05 PM

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 #! :cool:
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.
Posted Image


Here is the standard out result of the above query.
Posted Image

A simple script which demonstrates database access using Python. ;)
  • 2

#2 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 21 September 2009 - 05:13 AM

Very well done! +rep
  • 0

#3 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 21 September 2009 - 08:22 AM

Nice demo :)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download