Str-based ValueError when using SQLite3 to import values from a variable to a database table

Question

I'm trying to create a simple login program that saves the recorded username and password from variables into an SQLite3 database. Running the program using hardcoded strings works as expected, but when I try to use variables, a str-based TypeError occurs. I tried using str(variable), but that didn't work and I'm unsure what else could be problem. Any help would be appreciated.

import sqlite3
from sqlite3 import Error
import sys

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

def create_new_user(new1, new2):

    create_users =  ("""INSERT INTO users (username, password)
                       VALUES (?, ?)
                        ;""", str(new1), str(new2))

    execute_query(connection, create_users)   

create_users_table =  """CREATE TABLE IF NOT EXISTS users (
                                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                                        username TEXT NOT NULL,
                                        password TEXT NOT NULL
                                    ); """

execute_query(connection, create_users_table)

user = input("Would you like to create an account? ")
if "yes" in user:

    new1 = input("\nNew username: ")
    new2 = input("New password: ")
    create_new_user(new1, new2)

else:
    sys.exit(0)

Traceback (most recent call last):
  File "/Users/scnewmark/Documents/Database/database.py", line 62, in <module>
    create_new_user(new1, new2)
  File "/Users/scnewmark/Documents/Database/database.py", line 40, in create_new_user
    execute_query(connection, create_users)
  File "/Users/scnewmark/Documents/Database/database.py", line 18, in execute_query
    cursor.execute(query)
ValueError: operation parameter must be str

Edit the question to show the full traceback as properly formatted text. — Michael Butscher
– Michael Butscher, Commented Feb 24, 2020 at 23:02

blhsing · Accepted Answer · 2020-02-24 23:11:43Z

1

The execute method expects a SQL query string as the first argument and a tuple of parameters as the second argument, and yet with your:

create_users =  ("""INSERT INTO users (username, password)
                   VALUES (?, ?)
                    ;""", str(new1), str(new2))

and passing create_users as the query argument to do:

cursor.execute(query)

you are passing a tuple as the first argument to the execute method, resulting in the TypeError.

Instead, you can pass the query string and the parameters separately:

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

def create_new_user(new1, new2):

    query = "INSERT INTO users (username, password) VALUES (?, ?);"
    credentials = str(new1), str(new2))

    execute_query(connection, query, credentials)

answered Feb 24, 2020 at 23:11

blhsing

109k9 gold badges89 silver badges132 bronze badges

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

2 Comments

Alaina Over a year ago

That worked; thank you! Now one follow up question, is there a way to search the database using a something along the lines of if username in database: <run code>?

blhsing Over a year ago

You're welcome. You can refer to stackoverflow.com/questions/2440147/…

Collectives™ on Stack Overflow

Str-based ValueError when using SQLite3 to import values from a variable to a database table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related