CODEWITHSUNDEEP
pythonsqlsql-server
ktmac00
ktmac00

Reputation: 11

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

#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?

Upvotes: 0

Views: 137

Answers (2)

Majo
Majo

Reputation: 76

For the item in the tickList :

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

Upvotes: 0

maronavenue
maronavenue

Reputation: 246

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

Upvotes: 1

Related Questions