CODEWITHSUNDEEP
pythonsqlsqlitevariablesinsert
Lance Collins
Lance Collins

Reputation: 3435

INSERT INTO statement with variable in python

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!

Upvotes: 1

Views: 3242

Answers (2)

unutbu
unutbu

Reputation: 879271

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()

Upvotes: 2

haggai_e
haggai_e

Reputation: 4820

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.

Upvotes: 2

Related Questions