how do i insert values from python into sql server

Question

hi i am looking to insert these 3 values into my SQL database table that has columns: email, cardnumber, dateandtime here is my code:

email = input("Email: ")
cardnumber = int(input("Enter card number:"))
now = datetime.now()
now = now.strftime('%Y-%m-%d %H:%M:%S')
newrowforsql()

my code for the query is:

def newrowforsql():
    query = """\
        insert into table1 (email,cardnumber,dateandtime)
        values(email,cardnumber,now)"""
    insertnewrow = execute_query_commit(conn, query)

I cant seem to insert the values

my code for executing the query and committing it is:

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

You don't pass your variable that contains the values, just a string woth the variable names — azro, Jun 03 '20 at 10:39
Does this answer your question? [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — azro, Jun 03 '20 at 10:40

score 0 · Answer 1 · answered Jun 03 '20 at 10:53

As "azro" mentioned correctly you didn't put in the variable content to the query, you just put in the name of the variable which contains the information you want. What you need to change is the following:

def newrowforsql():
    query = """\
        insert into table1 (email,cardnumber,dateandtime)
        values(email,cardnumber,now)"""
    insertnewrow = execute_query_commit(conn, query)

to

def newrowforsql():
    query = """\
        insert into table1 (email,cardnumber,dateandtime)
        values({theEmail},{theCardnumber},{now})""".format(theEmail=email, theCardnumber=cardnumber, now=now)
    insertnewrow = execute_query_commit(conn, query)

This is one of the most used options to manipulate strings in python. But if you are using python3.7+ (maybe from Python3.6 and up, but I'm not sure) there is a much better and faster option to manipulate strings, it's name is "f-strings".

Here is the same solution but with f-strings instead of the method str.format

def newrowforsql():
    query = f"""\
        insert into table1 (email,cardnumber,dateandtime)
        values({email},{cardnumber},{now})"""
    insertnewrow = execute_query_commit(conn, query)

Good luck!

how do i insert values from python into sql server

1 Answers1