Getting error while inserting data in sqlite3

Question

I am new to Python and started off with sqlite. I have two csv transaction.csv and users.csv from where I am reading the data and writing to the sqlite database.Below is the snippet

import csv
import sqlite3 as db



def readCSV_users():
    with open('users.csv',mode='r') as data:
         dr = csv.DictReader(data, delimiter=',')
         users_data = [(i['user_id'], i['is_active']) for i in dr if i['is_active']=='True']
         #---------------------
    return users_data
    
def readCSV_transactions():
    with open('transactions.csv',mode='r') as d:
         dr = csv.DictReader(d, delimiter=',')
         trans_data = [(i['user_id'], i['is_blocked'],i['transaction_amount'],i['transaction_category_id']) for i in dr if i['is_blocked']=='False']
         #---------------------
    return trans_data
 
def SQLite_connection(database):
 
    try:
        # connect to the database
        conn = db.connect(database)
        print("Database connection is established successfully!")
        conn = db.connect(':memory:')
        print("Established database connection to a database\
        that resides in the memory!")
        cur = conn.cursor()
        return cur,conn
    except exception as Err: 
       print(Err)

      
def dbQuery(users_data,trans_data,cur,conn):
  try:
    cur.executescript(""" CREATE TABLE if not exists users(user_id text,is_active text); 
    CREATE TABLE if not exists transactions(user_id text,is_blocked text,transaction_amount text,transaction_category_id text);
    INSERT INTO users VALUES (?,?),users_data;
    INSERT INTO transactions VALUES (?,?,?,?),trans_data""")
    conn.commit()
    a=[]
    rows = curr.execute("SELECT * FROM users").fetchall()
    for r in rows:
        a.append(r)
    return a
  except Err: 
       print(Err)
  finally: 
      conn.close()
      

    


if __name__ == "__main__":
  database='uit'
 
  users_data=readCSV_users()
  trans_data=readCSV_transactions()
  curr,conn=SQLite_connection(database)
  print(dbQuery(users_data,trans_data,curr,conn))

But I am facing below error.I believe the ? is throwing the error in executescript

cur.executescript(""" CREATE TABLE if not exists users(user_id text,is_active text);
sqlite3.OperationalError: near "users_data": syntax error   

Any pointers to resolve this?

Answer 1

Putting users_data directly in query is wrong. It treats it as normal string.

But it seems executescript can't use arguments.
You would have to put values directly in place of ?.
Or you have to use execute()

cur.execute("INSERT INTO users VALUES (?,?);", users_data)
cur.execute("INSERT INTO transactions VALUES (?,?,?,?)", trans_data)