Populating Python variables from SQLite fields

[Greyhelm]

I am trying to understand using an SQLite database in Python and can grasp running queries, but would like to understand how to populate Python variables from SQLite fields. Here is my sample Python code:

import sqlite3

id = 0
name = ""
occupation = ""
location = ""

connection = sqlite3.connect("test.db")
cursor = connection.cursor()
sql = """SELECT * FROM People"""

cursor.execute(sql)

records = cursor.fetchall()

#record = data.select(sql)
for record in records:
    id = record[0]
    name = record[1]
    occupation = record[2]
    location = record[3]

connection.commit()
connection.close()

print "Current record:", id
print "Name: ", name
print "Occupation: ", occupation
print "Location: ", location

question = raw_input("Do you want to set location? ")
if question == "yes":
    location = "home"
    connection = sqlite3.connect("test.db")
    cursor = connection.cursor()
    cursor.execute(sql)
    sql = "UPDATE People SET location="home" WHERE id = "1""
    connection.commit()
    connection.close()

This assumes a SQLite database file test.db with a single table People that I would like to use to store some data, ID autoincrement field, NAME persons name, OCCUPATION, job, LOCATION where they are (home, work, etc).

In playing with the code, I was trying to read a record from SQLite and populate a set of variables to be displayed in Python. I would then like to be able to prompt the user for a change and use the key field (ID) to update the SQLite record. In this example, I only have one record (row) in the table and all fields are set to TEXT except the ID field (INTEGER AUTOINCREMENT).

Your assistance would be greatly appreciated.

[TrustyTony]

Maybe this would help you: http://stackoverflow.com/questions/811548/sqlite-and-python-return-a-dictionary-using-fetchone

[woooee]

You would first compare the ID returned to record[0], the original ID. If it has changed, you can use UPDATE

cur.execute("UPDATE People SET SQL_id_tag==:update_id",
       {"update_id": new_id_variable})
con.commit()

[Greyhelm]

TONYJV - thank you for the link, now to absorb all of that

WOOOEE - I have tried several ways to get your code to work, but the crazy thing is I am getting "syntax error - unindent does not match any outer indentation level"

import sqlite3

id = 0
name = ""
occupation = ""
location = ""

conn = sqlite3.connect("test.db")
c = conn.cursor()
c.execute('select * from People')

records = c.fetchall()

print records

for record in records:
    id = record[0]
    name = record[1]
    occupation = record[2]
    location = record[3]

print "Current record:", id
print "Name: ", name
print "Occupation: ", occupation
print "Location: ", location

question = raw_input("Do you want to set location? (y/n) ")
if question == "y":
    print "Current location is ", location
    newloc = raw_input("Change it to what (string)? ")
    if newloc != location:
        location = newloc
        print "Location has changed! ", location

       c.execute("UPDATE People SET SQL_id_tag == :update_id",
                 {"update_id": newloc})
       c.commit()

[woooee]

I am getting "syntax error - unindent does not match any outer indentation level"

Look at the line number of the error message (you didn't post it) and check that it, or sometimes the previous line, lines up correctly.

[Greyhelm]

Okay, so pasting might be a good Windows function, but for our example, it stunk.

I corrected the indentation and adjusted the code:

import sqlite3

id = 0
name = ""
occupation = ""
location = ""

conn = sqlite3.connect("test.db")
c = conn.cursor()
c.execute('select * from People')

records = c.fetchall()

print records

for record in records:
    id = record[0]
    name = record[1]
    occupation = record[2]
    location = record[3]

print "Current record:", id
print "Name: ", name
print "Occupation: ", occupation
print "Location: ", location

question = raw_input("Do you want to set location? (y/n) ")
if question == "y":
    print "Current location is ", location
    newloc = raw_input("Change it to what (string)? ")
    if newloc != location:
        location = newloc
        print "Location has changed! ", location

        c.execute('''UPDATE People SET location == :update_id''',
        {'update_id': newloc})

        conn.commit()
        conn.close()

raw_input("Press any key to exit")

It works, but my next question is, how do I choose to update a specific record in the database? I would think it would be as simple as:

c.execute('''UPDATE People WHERE id == "Mark" SET location == :update_id''',
        {'update_id': newloc})

and if there were multiple records, then it would only update the record where id == "Mark" is that correct?

[richieking]

Do this........

c.execute('UPDATE objects SET created=?,modified=? WHERE id=?',
        (data1,data2,data3))