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 name = record occupation = record location = record 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.