Insert variable Data into SQL server using python-CodePudding

def LiraRateApiCall():
R = requests.get(url)
timestamp = R.json()['buy'][-1][0]/1000
format_date = '%d/%m/%y'
date = datetime.fromtimestamp(timestamp)
buyRate = R.json()['buy'][-1][1]
print(date.strftime(format_date))
print(buyRate)


#ADDDING TO SQL SERVER
conn = odbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                    'Server=LAPTOP-36NUUO53\SQLEXPRESS;'
                    'Database=test;'
                    'Trusted_connection=yes;')
cursor = conn.cursor()
cursor.execute('''
                INSERT INTO Data_table (Time1,Price)
                VALUES
                ('date',140),
                ('Date2' , 142)
                ''')
conn.commit()
cursor.execute('SELECT * FROM Data_table')

for i in cursor:
    print(i)

How do i pass the variables date and buy rate to the table instead of putting in values liek i did (i put in'date' , 140 for example but i want to pass variables not specific values)

CodePudding user response：

You'll need to check the driver version that you're using, but what you're looking for is the concept of bind variables. I'd suggest you look into the concept of fast_executemany as well - that should help speed things up. I've edited your code to show how bind variables typically work (using the (?, ?) SQL syntax), but there are other formats out there.

def LiraRateApiCall():
R = requests.get(url)
timestamp = R.json()['buy'][-1][0]/1000
format_date = '%d/%m/%y'
date = datetime.fromtimestamp(timestamp)
buyRate = R.json()['buy'][-1][1]
print(date.strftime(format_date))
print(buyRate)


#ADDDING TO SQL SERVER
conn = odbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                    'Server=LAPTOP-36NUUO53\SQLEXPRESS;'
                    'Database=test;'
                    'Trusted_connection=yes;')
cursor = conn.cursor()

#Setup data
data = [('date',140), ('Date2' , 142)]

#Use executemany since we have a list
cursor.executemany('''
                INSERT INTO Data_table (Time1,Price)
                VALUES (?, ?)
                ''', data)

conn.commit()
cursor.execute('SELECT * FROM Data_table')

for i in cursor:
    print(i)

CodePudding user response：

I dont understand at all your question

If you want to pass the variables:

insert_sql = 'INSERT INTO Data_table (Time1,Price) VALUES ('   date   ','   str(buyRate)   ')'
cursor.execute(insert_sql)

If you want to do dynamic Insert:

You can only insert knowing the values or by inserting with a select

INSERT INTO table
SELECT * FROM tableAux
WHERE condition;

That or you could iterate through the fields you have in a table, extract them and compare it to your variables to do a dynamic insert.

With this select you can extract the columns.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'table1'