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](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html)? — Selcuk, May 24 '16 at 05:50
In Python, variable names don't start with an uppercase letter. Name them `sql`, `cursor`, `conn`. — , May 24 '16 at 05:57
I wrote this on my phone and automatically made them upper case. — Tom, May 24 '16 at 06:01
@Tom Please go through official documents before posting questions to Stackoverflow , otherwise you ll get block here . — not 0x12, May 24 '16 at 06:17
Possible duplicate of [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — Ilja Everilä, Jun 05 '18 at 10:43

score 2 · Answer 1 · answered May 24 '16 at 06:52

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 '16 at 06:52

Yuan Wang

145
4

Chathuranga · Answer 2 · 2016-05-26T08:41:26.023

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 '16 at 08:41

answered May 24 '16 at 06:13

Chathuranga

1,008
1
15
26

1

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. – user3159253 Apr 01 '21 at 19:31

d-coder · Answer 3 · 2020-12-11T11:50:18.163

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 '20 at 11:50

answered May 24 '16 at 06:07

d-coder

12,813
4
26
36

Thank you for the downvote. Please improve the answer if you think you can! – d-coder Jun 06 '18 at 08:43

score 0 · Answer 4 · answered Sep 14 '22 at 17:50

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

score -1 · Answer 5 · answered Apr 28 '21 at 03:55

-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 '21 at 03:55

zengse

11
2

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. – Tijmen Apr 01 '22 at 21:19

Using Python variables in MySQL insert statement

5 Answers5

Linked