Python Database Programming: Part Eight

Python database programming

Executing updateteam.py at the command line.

In the previous article, we introduced some more complex query operations using Sqlite. In this article, we will use Sqlite to both modify a table entry and delete a table entry.

Python Database Programming: Updating a Record

First, we need to enter the code for updating the player table, which we will call updateteam.py. We want to re-assign player Travis d’Arnaud to the Washington Nationals:

import sqlite3
import sys
conn = sqlite3.connect('my_database')
cursor = conn.cursor()
newteam = sys.argv[2]
player = sys.argv[1]
# Query to find the player ID:
query = """
select p.idnum
from user u, player p
where u.username=? and u.idnum = p.idnum
"""
cursor.execute(query,(player,));
for row in cursor.fetchone():
    if (row != None):
        playerid = row
# Now, modify the player:
cursor.execute("update player set team=? where idnum=?", (newteam,playerid))
conn.commit()
cursor.close()
conn.close()

When you run this script, you need to pass the name of the user/player to update, as well as the team ID number of the team to which you want to transfer the player. For example:

>python finduser.py travis
(u'travis', ':', u"Travis d'Arnaud", 'plays for the', u'New York Mets')

>python updateteam.py travis 20

>python finduser.py travis
(u'travis', ':', u"Travis d'Arnaud", 'plays for the', u'Washington Nationals')

The example output shows the before and after picture of the player row, verifying that the updateteam.py script work.

The updateteam.py script expects two values from the user: the user name of the player to update (in this case, travis) and the team number of the new team (in this case 20, the team id number of the Washington Nationals). This example also shows the use of the fetchone method on the Cursor. The final SQL statement then updates the player row for the given user to have a new manager.

Python database programming

Executing terminate.py at the command line.

Python Database Programming: Deleting a Record

The next example uses a similar technique to delete a player from the table.

import sqlite3
import sys
conn=sqlite3.connect('my_database')
cursor = conn.cursor()
player = sys.argv[1]
# Query to find the employee ID:
query = """
select p.idnum
from user u, player p
where u.username=? and u.idnum = p.idnum
"""
cursor.execute(query,(player,))
for row in cursor.fetchone():
    if (row != None):
        playerid = row
# Now, modify the employee:
cursor.execute("delete from player where idnum=?", (playerid,))
conn.commit()
cursor.close()
conn.close()

When you run this script, you need to pass the user name of the player to delete. You should see no output unless the script raises an error:

>python finduser.py travis
(u'travis', ':', u"Travis d'Arnaud", 'plays for the', u'Washington Nationals')
>python terminate.py travis
>python finduser.py travis
>

This script uses the same techniques as the updateteam.py script by performing an initial query to get the player ID number for the given user name and then using this ID number in a later SQL statement. With the final SQL statement, the script deletes the player from the player table.

External Links:

Database Programming at wiki.python.org

Database Programming at python.about.com

Databases at docs.python-guide.org

Be Sociable, Share!

Speak Your Mind

*