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: 107 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 ...

Creating a Database With Python [ID:740] (5/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 to recreate the simple example database we have used in this series from within a Python program. We use a live MySQL database command-line, to track changes made programmatically. A database is created, a table added to it, and user data inserted into that table. 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
# to create user (as root) with necessary privilages:
# mysql> grant all on *.* to test_user@localhost identified by 'showmedo' with grant option;
# import relevant module
import MySQLdb 

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

# test DB data to insert into new tables
# name, email, join-date, author-status
table_data = {
        'showmedo_user':(
        ('kyran dale', 'kg@showmedo.com', '2007-09-11',0),
        ('ian ozsvald', 'ian@showmedo.com', '2008-01-11',1),
        ('thomas eddison', 'tom@gec.com', '2007-11-24',1),
        ('richard coates', 'rc@tinburgen.org', '2008-04-22',0),
        ('karl von frisch', 'kvf@maxplank.ac.de', '2008-01-09',0),
        ('susan mortimer', 'suzie@backlogger.com', '2007-08-15',1),
        ('alan sussman', 'alan.sussman@gmail.com', '2007-07-15',0),
        ('bernard reeves', 'bernie@abc.com', '2007-07-23',0),
        ('phil tensing', 'phit@mmd.com', '2008-02-05',0),
        ('elaine dean', 'ellie@lotech.com', '2007-11-24',1),
        )
    }

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

# 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()

# demonstrate the creation of a database
#cursor.execute('show databases')
#prettyPrint(cursor.fetchall())
#cursor.execute('create database showmedo_test')
#cursor.execute('show databases')
#prettyPrint(cursor.fetchall())

## switch to using the newly-created database
cursor.execute('use showmedo_test')

# use the cursor to execute a 'create table' command
#cursor.execute("""
#CREATE TABLE showmedo_user(
  #id INTEGER  NOT NULL AUTO_INCREMENT,
  #name VARCHAR(255)  NOT NULL,
  #email VARCHAR(255),
  #join_date DATE,
  #author_status TINYINT(1),
  #PRIMARY KEY(id)
#)
#""")

# loop through our table-data, inserting items into the database
for data in table_data['showmedo_user']:
    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: 239 (since July 30th)
  • Plays in last week: 0
  • Published: 77 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

Good work Kyran! I have been doing some work with sqlite and Python recently, and most of the video applies to that db as well. I am glad that you are showing both the code, the Python shell, and the mysql shell.


Correction to my previous comment:

I was using Python 2.5 with the MySQL-python-1.2.2.win32-py2.5.exe. Sorry about that.


Thank you for this series. However, I think that Windows users will need to include a "db_connection.commit()" after any INSERT commands are issued to the database. Otherwise, they don't actually get placed in the table. It's rather odd. A "Select * from the_table" using the python API will show the results of your INSERT command, but when I query the table after the script finishes, the results are not there. Another quirk, is that my auto_increment field behaves as if there were a row inserted but then deleted.

Hopefully this helps some others. I was using windows XP with python 2.6 and MySQLdb MySQL-python-1.2.2.win32-py2.5.exe installer.


Great lessons!!


Great series - saved me some time!


Great Stuff! I hope to put together a nice GUI for a database using this and the other vids. Cheers!


Nice and to the point, that's all I needed to get my a$$ away from M$ C# and vb.net :P Thank you for your valuable video series.


8. anonymous Wed, 24 Sep 2008 01:38

I want to create a database using MySQLDb taking input from the user can u show me a sample code for doing it.


@collardb - thanks for the compliment. Are you having a problem with the mysql command-line or while running a Python script? If you have any error-messages etc. that would help diagnose the problem.

I'm afraid I don't understand 'enurmating' - did you mean enumerating? If you can provide a little more detail I'll try and help. Usually problems with insertion etc. involve privileges - you might have to specify that the user can insert into the database by using a GRANT command. For example:

GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';


Great vids!

However, I'm having a problem inserting into the db. My user is 'root' and the db seems to be enurmating after each run. ( I've copied the insert data into int othe cmd line and that worked). so was wondering if you know what a common cause is. thx!


Thank you very much.

This series is very informative for me. I do not know if this is a right place to ask a question, but it may help others.

I wrote a product recommendation engine (very simple) for an e-commerce application using the earlier introductory lessons. It works well at the command level. Given the customer ID, it prints recommended products. But I want to extend this toy to use databases, GUI, and web framework. An example would be, if I enter a customer ID, it outputs the recommended products that I didn't buy yet in the browser or GUI. This new exercise will help integrate many things I learned at ShowMeDo lessons.

Thank you a million again for such a great series.

Nagu


thanks steve,

funnily enough there is exactly that - a wxpython+DB (datagrid) - demo coming soon. It was quite difficult finding any examples so should prove useful I hope. It will follow (gently) the construction of a wx-DB interface


Good job so far. It is a huge subject so I hope you have time to do many more demos. Any possibility of eventually rolling this into a small wxPython app that shows how to databind to controls such as textfields and grids, and how to navigate, add and delete records? I am thinking along the lines of Ian's excellent Image Viewer demo.

Thanks again.

Steve Rose


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

Related Videos

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