-1

I've recently started learning and trying to apply SQL into my python code and came across this problem; I can't figure out a way to properly insert variables into a table using the set up I have. This setup came from a SQL library tutorial on realpython.com (https://realpython.com/python-sql-libraries/), and judging by the different formats of sqlite setups, I feel like it isn't too efficient.

import sqlite3
from sqlite3 import Error

# - - - - SQL DATABASE SETUP - - - - #
def create_connection(path):
    connection = None
    try:
        connection = sqlite3.connect(path)
        print("Connection to SQLite DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection

connection = create_connection("databse.sqlite") # Creates the connection

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

create_users_table = """
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER,
  grade INTEGER,
  gender TEXT,
  nationality TEXT
);
"""
execute_query(connection, create_users_table)  

      
# - - - - ^ SQL DATABSE SETUP ^ - - - - #

I believe I understand everything going on in these two functions, although I noticed that not many use this way, and I think that's why I couldn't find an answer to my problem, I would appreciate any advice on more efficient setups.

I tried using a solution that used the q-mark style in order to insert variables, but it didn't work for me. I think it could be because of the triple quotes strings, and the query statement being a parameter of a function within a function.

First attempt looked like this

name = str(input("Hi! What is your name?\n"))

store_name = ("""
  INSERT INTO users (name)
  VALUES (?)
""", name)

execute_query(connection, store_name)

Which gave me the error "ValueError: operation parameter must be str"

I later tried to remove the variable and just plug in the query as a single line statement, enclosed within a parameter

execute_query(connection, ("INSERT INTO users (name) VALUES (?)", name))

Same error, might be a simple mistake on my end that I'm not seeing.

And again if this setup isn't efficient, I would appreciate any advice.

Obb-77
  • 44
  • 7

1 Answers1

-1

you need to use same query as MySQL. For example - "INSERT INTO table (column1,column2 ,..) VALUES( value1, value2 ,...)"

amansworld
  • 1
  • 1