How can I insert a python list into a SQL Server database?

Question

#function used to get tickers from the tweets
def getTicker():
 for tweet in tweets:
 if "$" in tweet.text:
 x = tweet.text.split()
 for i in x:
 if i.startswith("$") and i[1].isalpha():
 tickList.append(i)
#running the ticker function
getTicker()
print(tickList)
 
#connecting to the database
conn = pyodbc.connect(
 "Driver={SQL Server};"
 "Server=SERVERNAME;"
 "Database=DBNAME;"
 "Trusted_Connection=yes;")
#query to put the tickers into the
cursor = conn.cursor()
# print(var_string)
cursor.execute('INSERT INTO master.dbo.TickerTable (TickerName) VALUES (?);', [','.join(tickList)])
conn.commit()

This inserts a list of tickers into the table but they all go in as one item instead of individually. Is there a way that I can take the list and insert the one by one?

• rdas: ....use a loop?

Answer 1

You are plugging the entire joined string value inside the parametrized query: VALUES (?);. You can either retain the same logic and introduce a loop which executes one SQL operation for each ticker, or you could adjust your string formatting to include wrapping parentheses to VALUES ?; per se:

INSERT INTO master.dbo.TickerTable (TickerName) VALUES (ticker_1),(ticker_2),(ticker_3);

Answer 2

For the item in the tickList :

cursor.execute('INSERT INTO master.dbo.TickerTable (TickerName) VALUES (?);', item)