[Tutor] Data conversion

[Peter Otten]

Joe Aquilina wrote:

> I am new to this list and very much a beginner to Python. Please excuse
> me if this is a silly question, but in all my searches this morning I
> have not been able to find an answer.
> 
> I have a (single table) database file (SQLite3). It has one table, call
> it literature, with an integer, autoincrement primary key field. I have
> created a data entry form in Python that I want to use to enter new rows
> into this database file. On the data entry form I will enter the values
> for a new table row - all except the primary key field.
> 
> What I want to be able to do is to have my data entry form autoincrement
> this primary key field for me, with no ability to change the contents on
> the data entry form, and save this incremented value as the value of the
> num field when I save the new row.
> 
> So for example, if the last row in the table has a value of 256 in the
> num field, I want the value of 257 to be saved as the value of the num
> field into the new row I am adding, without having to see or or enter
> this new value (or indeed the previous value) on the data entry screen.
> 
> I hope this makes sense. But how do I do this?
> 
> I thought about doing a
> 
> SELECT num FROM literature;
> 
> from the table, then getting the contents of the num field of the last
> row in the data that a fetchall() retrieves and incrementing it to save
> with the new row.
> 
> However, the fetchall() returns the data as tuples, not integers and I
> don't know how to convert from a tuple data type to an integer to make
> this work.
> 
> Is this possible? Or can I achieve my objective in some other way?

I may be misunderstanding you, but the point of an autoincrement field is 
that you don't have to set its value, the database will do it automatically 
for you. A minimal example:

$ cat autoinc.py
import sqlite3

db = sqlite3.connect("tmp.db")
cs = db.cursor()

cs.execute("""create table if not exists items
    (item_id integer primary key autoincrement, name)""")

while True:
    name = raw_input("Enter name ")
    if not name:
        break
    cs.execute("""insert into items (name) values (?)""", (name,))
db.commit()

for row in cs.execute("select * from items"):
    print row

$ python autoinc.py
Enter name alpha
Enter name beta
Enter name gamma
Enter name
(1, u'alpha')
(2, u'beta')
(3, u'gamma')
$ python autoinc.py
Enter name delta
Enter name epsilon
Enter name
(1, u'alpha')
(2, u'beta')
(3, u'gamma')
(4, u'delta')
(5, u'epsilon')
$

Even though item_id doesn't appear in the insert statement you get the 
desired consecutive values...