在python中向SQL表中输入数据？

Question

我正在制作一个连接到SQL数据库的程序，以便输入数据。我不想手动将数据输入表中，而是想让一个程序来完成。它会问我一系列问题，然后把答案输入表格。我不知道该怎么做。我的问题是在最后执行游标

我不确定如何将输入答案合并到执行函数中。是这样的吗？.format显示为字符串，因此我不确定如何实现它

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

代码如下：

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

上面的INSERT INTO代码片段实际上是有效的，但我需要手动输入值。那么，如何在该字符串中获取变量输入（类别、描述、日期等）

Answer 1

试试这个

在这里，您需要提供要插入的变量数据，还需要在单引号中添加{}，如{}。 因此，以“{}”格式提供值后，格式（“category_input”）看起来像“category_input”，如果有数字，则不起作用

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