INSERT INTO statement with variable in python

Question

I'm trying to insert the results of a query into a table, but add a variable to the result set . I've looked at some similar questions and I think I am representing the query variables correctly but I am getting a "global variable not defined" error. Can you point me in the right direction? I'm guessing it has to do with the fact that I am trying to represent a SELECT statement with a variable, but I'm not sure how the syntax should look.

def main():
    datadump()
    d = datetime.date.today()
    queryCurs.execute('SELECT * FROM data ORDER BY ac')
    sqlresults = queryCurs.fetchall()
    x,y,z = sqlresults
    queryCurs.execute('INSERT INTO history VALUES (?,?,?,?)',
    (d, x, y, z))
    createDb.commit

Thanks!

Asking questions about errors without providing traceback is pointless. — Cat Plus Plus
– Cat Plus Plus, Commented Oct 8, 2011 at 16:29
Your tuple contains (d, a, ac, am) but I don't see any of those (other than d) defined anywhere in main(). That seems like a good place to start. — eldarerathis
– eldarerathis, Commented Oct 8, 2011 at 16:30
here is the error: NameError: global name 'a' is not defined — Lance Collins
– Lance Collins, Commented Oct 8, 2011 at 16:34
The reason I didn't define it is because I wanted the results from the sqlresults assigned to d,a,ac,am. I thought I had to do this because of the date variable I was adding to the result set. — Lance Collins
– Lance Collins, Commented Oct 8, 2011 at 16:36

unutbu · Accepted Answer · 2011-10-09 12:55:15Z

2

You don't need to make two SQL queries to do this. Instead, use an INSERT query of the form INSERT INTO ... SELECT ...:

conn=sqlite3.connect(...)
cursor=conn.cursor()

today = datetime.date.today()
sql='''
    INSERT INTO history
    SELECT ?, foo, bar, baz
    FROM data
    ORDER BY ac
'''
cursor.execute(sql,[today])
conn.commit()

edited Oct 9, 2011 at 12:55

answered Oct 9, 2011 at 0:52

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

haggai_e · Accepted Answer · 2011-10-08 17:24:34Z

2

The execute method doesn't return a value. You would need to use fetchone or fetchall in order to get the results from the first query.

answered Oct 8, 2011 at 17:24

haggai_e

4,8491 gold badge29 silver badges42 bronze badges

2 Comments

Lance Collins Over a year ago

I added the fetchall method. However, I cannot seem to figure out a way to get each tuple from the query result into the history table and adding the date to the table at the same time. When I unpack the tuple and assign the values to x,y,z I get an error because there are 4 tuples the result from the fetch all method. I'm probably making this way too complicated, but I just want to get the results from the first query into another table and add a date to each row.

haggai_e Over a year ago

Well, the accepted answer seems like a great way to do that. If you do need to further process the first query results in python, you would need to process the list returned by fetchall, i.e. in a for loop, and insert each tuple with a separate INSERT INTO query. You could also use executemany to run the insertion queries in a single command.

Collectives™ on Stack Overflow

INSERT INTO statement with variable in python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related