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?

....use a loop?

rdas
– rdas

2020-10-23 20:25:14 +00:00
Commented Oct 23, 2020 at 20:25 — rdas
– rdas, Commented Oct 23, 2020 at 20:25

maronavenue · Accepted Answer · 2020-10-23 20:38:51Z

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

answered Oct 23, 2020 at 20:38

maronavenue

2461 silver badge4 bronze badges

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

Comments

ucMax · Accepted Answer · 2020-10-24 01:35:36Z

0

For the item in the tickList :

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

edited Oct 24, 2020 at 1:35

ucMax

5,5615 gold badges52 silver badges57 bronze badges

answered Oct 23, 2020 at 20:35

Majo

764 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related