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 — M Z
– M Z, Commented Dec 9, 2020 at 5:10

Dale K · Accepted Answer · 2021-02-11 02:53:18Z

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, 2021 at 2:53

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 9, 2020 at 6:15

Vishal Koli

563 bronze badges

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

2 Comments

TJ Struck Over a year ago

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!

Vishal Koli Over a year ago

Good to hear this!

Samuel Gottipalli · Accepted Answer · 2023-08-19 20:36:07Z

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.

Collectives™ on Stack Overflow

Enter data into a SQL table in python?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related