Enter data into a SQL table in python?

Question

I'm making a program that connects to a SQL database for me to enter in data. Instead of entering data into the table manually, I wanted to make a program do it. So it's asks me a series of questions, then inputs the answers into the table. I am not sure how to do that. My issue is at the end with the cursor execute.

I am not sure how I can incorporate the input answers into that execute function. Would it be something like this? The .format is showing up as a string, so I am not sure how to implement this.

VALUES
('{}'.format(category), '{}'.format(description), '{}'.format(date), '{}'.format(price), '{}'.format(vehicle))

Here is the code below:

import time
import pyodbc
conn = pyodbc.connect('Driver={SQL Server};'
                  'Server=TJDESKTOPPC;'
                  'Database=carparts;'
                  'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('''
              SELECT * FROM carparts.dbo.modifications
           
               ''')
conn.commit()

# Menu starts below

database = "carparts"
print("Welcome to the program!")
print()
print("You are connected to {} database".format(database))
print()
print()
print("The current columns in the table are...")
print()
conn = pyodbc.connect('Driver={SQL Server};'
                  'Server=TJDESKTOPPC;'
                  'Database=carparts;'
                  'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM carparts.dbo.modifications where 1=2')
headers = [i[0] for i in cursor.description]
print(headers)
print()
print("Categories are: engine, suspension, exhaust, or transmission")
print()
category = str(input("Please enter category: "))
print()
description = str(input("Please enter the description of the part: "))
print()
purchase_date = input("Please enter the purchase date in (YYYY-MM-DD): ")
print()
price = int(input("Please enter the price amount: "))
print()
vehicle = str(input("What vehicle is this for? (Model): "))
print()
print("Thanks!")
time.sleep(3)
print("\n" * 5)  # This will the clear screen of any previous code
print("Adding the category, description, purchase date, price, and vehicle to the table...")
time.sleep(2)

cursor.execute('''
            INSERT INTO carparts.dbo.modifications (category, description, purchase_date, price, 
vehicle)
            VALUES
            ('exhaust', 'Flowmaster Cat-back Exhaust', '2015-12-08', '551', 'focus_st')
           ''')
conn.commit()

The snippet above for INSERT INTO actually works, but I need to put the values in manually how it is. So how do I get the variable input (category, description, date, etc) in that string?

Does this answer your question? [pyodbc - How to perform a select statement using a variable for a parameter](https://stackoverflow.com/questions/9518148/pyodbc-how-to-perform-a-select-statement-using-a-variable-for-a-parameter) — M Z, Dec 09 '20 at 05:10

score 2 · Accepted Answer · edited Feb 11 '21 at 02:53

2

Try this,

Here you need to provide your variable data you want to insert and also need to add {} in single quotes like this '{}'. So that your after providing value in format "'{}'".format("category_input") is looks like 'category_input' and it doesn't effect if you have a number.

cursor.execute('''INSERT INTO carparts.dbo.modifications (category, description, 
    purchase_date, price, vehicle) VALUES ('{}', '{}', '{}', '{}', '{}')'''.format(category, description, purchase_date, price, vehicle))

edited Feb 11 '21 at 02:53

Dale K

25,246
15
42
71

answered Dec 09 '20 at 06:15

Vishal Koli

56
3

Thank you SO MUCH! I had just missed the spot where the .format needed to go! Thank you so much Vishal, this is working flawlessly now! – TJ Struck Dec 09 '20 at 20:22
Good to hear this! – Vishal Koli Dec 10 '20 at 04:44

score 0 · Answer 2 · answered Aug 19 '23 at 20:36

While .format() is a perfectly good way to achieve this, it may get complicated if you have to edit the string later on. (specially if someone else is trying to read/modify your code)

There are 2 alternate ways to achieve the same thing.

Named formatting - where the placeholders are named and called in the format()

e.g.:

cursor.execute("""INSERT INTO 
    carparts.dbo.modifications 
    (category, description, purchase_date, price, vehicle) VALUES ('{category}', '{description}', '{purchase_date}', '{price}', '{vehicle}')"""
    .format(category=category, description=description, purchase_date=purchase_date, price=price, vehicle=vehicle))

This gets a little difficult to look at as it becomes more and more lengthy, leading me to the next alternative

f-String - where the placeholders themselves are variables and is more naturally legible. This is also currently the preferred way of working with dynamic strings.

e.g.:

cursor.execute(f"""INSERT INTO 
    carparts.dbo.modifications 
    (category, description, purchase_date, price, vehicle) VALUES ('{category}', '{description}', '{purchase_date}', '{price}', '{vehicle}')""")

P.S. Not sure why the code section is behavior is weird but any valid IDE should accept the code and should also pass all(most) formatters.

Enter data into a SQL table in python?

2 Answers2