Inserting variable values into MySQL database; I'm using (%s) but getting syntax error

Question

table name: 'tickers_list' (table has only 1 column)

column name: 'Ticker'

The following works:

sql = "INSERT INTO tickers_list (Ticker) VALUES ('AAPL')"
mycursor.execute(sql)
mydb.commit()

But when I try to set it up to accept variables, it's not working:

symbol = 'AAPL'
sql = "INSERT INTO tickers_list (Ticker) VALUES (%s)"
mycursor.execute(sql, symbol)
mydb.commit()

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s)' at line 1

Where am I going wrong?

If your values of tickets are numbers, use %d.

FairOPShotgun
– FairOPShotgun

2021-05-25 01:06:32 +00:00
Commented May 25, 2021 at 1:06 — FairOPShotgun
– FairOPShotgun, Commented May 25, 2021 at 1:06
The tickers are strings, such as AAPL, MSFT, and so on.

jub
– jub

2021-05-25 01:49:41 +00:00
Commented May 25, 2021 at 1:49 — jub
– jub, Commented May 25, 2021 at 1:49

tadman · Accepted Answer · 2021-05-25 18:50:06Z

1

You need to execute with a tuple, and a tuple must contain at least one ,. In Python for a single value this means:

mycursor.execute(sql, (symbol,))

Which does look a bit weird, but it's just how it is. For multiple values it looks more normal, no trailing , is necessary.

edited May 25, 2021 at 18:50

answered May 25, 2021 at 2:13

tadman

212k23 gold badges237 silver badges266 bronze badges

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

1 Comment

jub Over a year ago

thanks! Haven't run into this before b/c I had only inserted multiple values prior to this, so those were tuples w/o my realizing it.

Rick James · Accepted Answer · 2022-08-05 03:56:22Z

0

In addition to what tadman says, strings need quotes:

... ("%s") ...

answered Aug 5, 2022 at 3:56

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Inserting variable values into MySQL database; I'm using (%s) but getting syntax error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related