Join Club ShowMeDo to Learn Python!

This series is a part of Club ShowMeDo. Click to learn why you should join our club with a simple monthly subscription.

  • We teach Python and all the cool modules
  • Topics include beginner Python, GUIs, Web and Desktop Apps
  • Long, specially crafted video tutorials just for you
  • Club video tutorials extend our normal Free content
  • Save your time - we've done the research for you
  • Keep your skills up-to-date
  • Learn at your own pace, everything you need is shown to you
  • Over 12 hours of archived material are waiting for you in the club

Name: [008] Kyran Dale
Member: 105 months
Authored: 16 videos
Description: I'm one of the co-founders of Showmedo and an academic researcher by profession. My peripatetic path has taken me from a Philosophy degree via Artificial Intelligence and Evolutionary Robotics to a PhD in Neuroscience. I spent five years in a small room with a colony of reluctant bumble-bees, becomi ...

Using and Manipulating a Database from Within a Python Progam [ID:739] (4/5)

in series: An Introduction to Database Programming with Python

(Showmedo is undergoing major changes. To report any problems viewing the videos please email us and include browser and OS specifics. Cheers - Kyran.)

In this video we show how Python can programmatically fetch and manipulate data from within a database and insert data into it. We build up simple examples and, using a live MySQL database command-line, demonstrate comparable queries and changes made to the database. As with all videos in this series, the lesson is applicable to most of the SQL based databases including PostgreSQL, SQLite, Microsoft SQL Server and Oracle, among others.

# Most common DBAPI conversion modules
# MySQL - MySQLdb
# PostrgeSQL - psycopg(2)
# SQLite - sqlite3
# Oracle - oracle
# M$ SQL server - adodbapi

# import relevant module
import MySQLdb 

# test variables for database access
HOST = 'localhost'
USER = 'test_user'
PASSWD = 'showmedo'
DATABASE = 'showmedo_test'

# make a connection to the DATABASE database
# if we were using, for example, a Postrgres database this line would start:
# db_connection = psycopg.connect( ...
db_connection = MySQLdb.connect(
        host=HOST,
        user=USER, 
        passwd=PASSWD, 
        db=DATABASE)

# we'll need a cursor to this database to execute commands
cursor = db_connection.cursor()

def prettyPrint(data):
    print "There are %d data items"%len(data)
    for i,d in enumerate(data):
        print "%d --- "%i, d

# some example queries:
# 1. execute query
#cursor.execute('select * from showmedo_user')
# 2. fetch and print buffered data
#prettyPrint(cursor.fetchall())

# fetching data from the query result set
cursor.execute('select * from showmedo_user')
# fetchone fetches one row, fetchall all remaining rows
#cursor.fetchone()
#cursor.fetchone()
#prettyPrint(cursor.fetchall())

# equivalent sorting of a table using SQL and python
#import operator
#cursor.execute('select * from showmedo_user order by name')
#prettyPrint(cursor.fetchall())

#cursor.execute('select * from showmedo_user')
#db_data = cursor.fetchall()
#db_data_sorted = sorted(db_data, key=operator.itemgetter(2))
#prettyPrint(db_data_sorted)

# inserting data (a user in this case) into a table
# best to construct complex strings before passing to the cursor for execution
data = ('a.n.other', 'annon@acme.com', '2008-6-12',1)
qstr = "INSERT INTO showmedo_user " +\
            "(name, email, join_date, author_status) values ('%s', '%s', '%s', %d)"%(data[0], data[1], data[2], data[3])
print qstr         
cursor.execute(qstr)


Got any questions?

Get answers in the ShowMeDo Learners Google Group.

Video statistics:

  • Video's rank shown in the most popular listing
  • Video plays: 352 (since July 30th)
  • Plays in last week: 0
  • Published: 75 months ago

Thank-yous, questions and comments

If this video tutorial was helpful please take some time to say thank-you to the authors for their hard work. Feel free to ask questions. Let the author know why their video tutorial was useful - what are you learning about? Did the video tutorial save you time? Would you like to see more?

You may also want to see our ShowMeDo Google Group to speak to our active users and authors.

Your email address will not be published.

Show some quick comments >>








All comments excluding tick-boxed quick-comments

Very illustrative, I would like to see more. Perhaps some of this focused on the installations of different drivers? And also as mentioned, high level library use.

Regards

Evaristo


Nice video Kyran!


Thanks I will try to use this to create a database to allow my company to search through the email logs easier and faster.


Your video has been edited. This is an automatic post by ShowMeDo.


Video published, thanks for contributing to ShowMeDo


Showmedo is a peer-produced video-tutorials and screencasts site for free and open-source software (FOSS)- with the exception of some club videos, the large majority are free to watch and download.

how to help » about » faq »

Educating the Open-source Community With Showmedo

Although as important as the software it supports, education and documentation are relatively neglected in the Open-source world. Coders love to code, and explaining how best to use or improve the software tends to be deferred or even sidelined.

At Showmedo we believe the community can play a vital role here and also say thanks for the tools and software that make our lives easier. If you have a piece of software you love or a programming langugage you are enthusiastic about, why not make a screencast showing others how to use it? All the stuff you wish you'd been told, the tips, tricks, insights that would have saved you time and frustration.

Screencasting is easier than you think, and we're happy to help you. You can emailus for advice or just use some of the how-to screencasts on the site. This screencasting learning-pathis a good place to start.

Kudos and Thanks for Kyran

Content

Feedback

Showmedo's development is fairly rapid and bugs will inevitably creep in. If you have any problems please drop us a line using the contact address below. Likewise, any suggestions for improvements to the site are gratefully received.

feedback@showmedo.com