Using Python variables in MySQL insert statement

Question

I've been trying for a while now and have look online and can't figure it out.

Variables are numbers and animals

sql = ("INSERT INTO favourite (number, info) VALUES (numbers, animals  )")
cursor.execute(*sql)
conn.comit()

Where did you look online? Did you read official MySQL Connector documentation? — Selcuk
– Selcuk, Commented May 24, 2016 at 5:50
In Python, variable names don't start with an uppercase letter. Name them sql, cursor, conn. — user5547025
– user5547025, Commented May 24, 2016 at 5:57
I wrote this on my phone and automatically made them upper case. — Tom
– Tom, Commented May 24, 2016 at 6:01
@Tom Please go through official documents before posting questions to Stackoverflow , otherwise you ll get block here . — Kalanamith
– Kalanamith, Commented May 24, 2016 at 6:17
Possible duplicate of How to use variables in SQL statement in Python? — Ilja Everilä
– Ilja Everilä, Commented Jun 5, 2018 at 10:43

Yuan Wang · Accepted Answer · 2016-05-24 06:52:46Z

2

sql = ("INSERT INTO favourite (number, info) VALUES (%s, %s)", (numbers, animals))

for safety, always use escape, see http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

answered May 24, 2016 at 6:52

Yuan Wang

1554 bronze badges

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

Comments

Chathuranga · Accepted Answer · 2016-05-26 08:41:26Z

1

Use:

sql=("INSERT INTO favourite (number, info) VALUES ({},{})".format(numbers,animals))

Its always good to use format as per future references. Check https://docs.python.org/release/3.1.5/library/stdtypes.html#old-string-formatting-operations

edited May 26, 2016 at 8:41

answered May 24, 2016 at 6:13

Chathuranga

1,0461 gold badge15 silver badges27 bronze badges

1 Comment

user3159253 Over a year ago

Don't insert parameters into an SQL statement manually. You need proper quoting of the values, and a chosen SQL connector library always provides this functionality.

d-coder · Accepted Answer · 2020-12-11 11:50:18Z

0

I think the following should work. Let me know how it works out for you.

sql=("INSERT INTO favourite (number, info) VALUES ({},{})".format(numbers,animals))

edited Dec 11, 2020 at 11:50

answered May 24, 2016 at 6:07

d-coder

14.2k4 gold badges28 silver badges39 bronze badges

1 Comment

d-coder Over a year ago

Thank you for the downvote. Please improve the answer if you think you can!

Gokuhan21 · Accepted Answer · 2022-09-14 17:50:39Z

0

    sql = "INSERT INTO favourite (number, info) VALUES (%s, %s)"
    val = (numbers, animals)
    cursor.execute(sql, val)
    conn.commit()

This works I just tested. Also you mispelled commit idk if that was intentional..

incase you dont have the top connection part right here

    db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="password",
            database="database"
            )

    cursor = db.cursor()

answered Sep 14, 2022 at 17:50

Gokuhan21

231 silver badge7 bronze badges

Comments

zengse · Accepted Answer · 2021-04-28 03:55:39Z

-1

Here is alternative solution which works for me

cursor = conn.cursor()
query ="INSERT INTO favourite (number, info) VALUES ('"+ variable1  +"','"+  variable2+"')"
cursor.execute(query)
conn.commit()

answered Apr 28, 2021 at 3:55

zengse

212 bronze badges

1 Comment

Tijmen Over a year ago

This is a very dangerous method, because it doesn't escape anything. Hence, if your variable includes any SQL characters, such as ;, it will be interpreted as part of the statement. This means your code is now vulnerable to SQL injection. If your variable1 is populated with ; DROP TABLE favourite;, it would delete the entire table and it's contents.

Collectives™ on Stack Overflow

Using Python variables in MySQL insert statement

5 Answers 5

Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related