trying usage-guide tutorial code

giuseppe massimo bertani <[email protected]> Tue, 29 May 2007 15:10:44 +0200

Newsgroups	gmane.comp.python.db.pysqlite.user
Message-ID	<[email protected]>

Hello all, 

I've just started to experiment sqlite through python 2.4.2 on a Suse10.1 box. The pysqlite documentation was not present with
the packet, so I had to download it from http://initd.org/tracker/pysqlite.

After a look at the tutorial in the user guide, I arranged the examples showed in a test.py file 
and executed it in order to produce the same printouts
described in the tutorial. 

Sadly, the example 4 fails in this way:
---------------------------------
#Example 4
#Let's insert more people into the people table:
 
newPeople = (('Lebed', 53), ('Zhirinovsky' , 57))
 
#for person in newPeople:
#    cur.execute("insert into people (name_last, age) values (?,?)", person)

cur.executemany("insert into people (name_last, age) values (?, ?)", newPeople)

# The changes will not be saved unless the transaction is committed explicitly:
con.commit()
-----------------------------------
gbertani@gbertani:sqlite>python test.py
[('Putin', 51), ('Yeltsin', 72)]
Putin is 51 years old.
Yeltsin is 72 years old.
Putin is 51 years old.
Yeltsin is 72 years old.
Traceback (most recent call last):
  File "test.py", line 63, in ?
    cur.executemany("insert into people (name_last, age) values (?, ?)", newPeople)
  File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 276, in executemany
    self.execute(query, _i)
  File "/usr/lib/python2.4/site-packages/sqlite/main.py", line 255, in execute
    self.rs = self.con.db.execute(SQL % parms)
TypeError: not all arguments converted during string formatting
gbertani@gbertani:sqlite>  

-----------------------------------

Above is showed the version using executemany(), but the for loop with execute() fails in the same way. The unique way
I found to add rows in the table is to use an unique string argument to execute(), that means embed the argument in the sql string.
Each time I try to use the second argument of execute() or executemany() I got the error.

In attachment, you can find my test.py.

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite

test.py (application/x-python, 2.6 KB)

#! /usr/bin/env python
#
# -*- coding: iso-8859-1 -*-
#
# GMB 2007/5/29 adapted from pysqlite usage-guide.html#brief-tutorial
#


import sqlite

#2.1 Connecting to a Database
#Example 1
#Connecting to a database file mydb:
#con = sqlite.connect("mydb")

#Example 2
#Creating an in-memory database:
con = sqlite.connect(":memory:")

#2.2 Executing SQL statements
#For this section, we have a database mydb defined and populated by the following SQL code:

CMD = "create table people (name_last varchar(20), age integer)"
cur = con.cursor()
cur.execute(CMD)    

CMD = "insert into people (name_last, age) values ('Yeltsin',   72)"
cur.execute(CMD)    
CMD = "insert into people (name_last, age) values ('Putin',     51)"
cur.execute(CMD)    

#Example 1
#This example shows the simplest way to print the entire contents of the people table:
 
# Execute the SELECT statement:
cur.execute("select * from people order by age")

# Retrieve all rows as a sequence and print that sequence:
print cur.fetchall()


#Example 2
#Here's another trivial example that demonstrates various ways of fetching a single row at a time from a SELECT-cursor:

SELECT = "select name_last, age from people order by age, name_last"

# 1. Iterate over the rows available from the cursor, unpacking the
# resulting sequences to yield their elements (name_last, age):
cur.execute(SELECT)
for (name_last, age) in cur:
    print '%s is %d years old.' % (name_last, age)

# 2. Equivalently:
cur.execute(SELECT)
for row in cur:
    print '%s is %d years old.' % (row[0], row[1])    


#Example 4
#Let's insert more people into the people table:
 
newPeople = (('Lebed', 53), ('Zhirinovsky' , 57))
 
#for person in newPeople:
#    cur.execute("insert into people (name_last, age) values (?,?)", person)

cur.executemany("insert into people (name_last, age) values (?, ?)", newPeople)

# The changes will not be saved unless the transaction is committed explicitly:
con.commit()
    
#Example 3
#The following program is a simplistic table printer (applied in this example to people)

FIELD_MAX_WIDTH = 20
TABLE_NAME = 'people'
SELECT = 'select * from %s order by age, name_last' % TABLE_NAME

cur.execute(SELECT)

# Print a header.
for fieldDesc in cur.description:
    print fieldDesc[0].ljust(FIELD_MAX_WIDTH) ,
print # Finish the header with a newline.
print '-' * 78

# For each row, print the value of each field left-justified within
# the maximum possible width of that field.
fieldIndices = range(len(cur.description))
for row in cur:
    for fieldIndex in fieldIndices:
        fieldValue = str(row[fieldIndex])
        print fieldValue.ljust(FIELD_MAX_WIDTH) ,
    print # Finish the row with a newline.