Passing variables into a MySQL query using python

Question

I have a database with some records that have a date field of "05221999". I am trying to do a SQL query from the input of the user based on just the month and year. In this case I am interested in all the records with the month of 05 and the year of 1999.

Unfortunately, I can't get the Python/SQL syntax correct. Here is my code so far:

    def submitact(self):
        date = self.md.get()
        month = date[0:2]
        year = date[2:7]

        db = pymysql.connect("localhost", "username", "password", "database")
        cursor = db.cursor()
        cursor.execute("SELECT * FROM `table` WHERE `Code` = 'RM' AND `Date` LIKE %s'_'%s", (month, year))

        results = cursor.fetchall()
        print(results)

        cursor.close()
        db.close()

I've done several variations on the SELECT statement and they either return errors or nothing.

Thanks!

Does this answer your question? [How to pass variables to MYSQL using Python](https://stackoverflow.com/questions/18833865/how-to-pass-variables-to-mysql-using-python) — Henry Woody, May 21 '22 at 01:15

score 1 · Accepted Answer · answered Dec 08 '20 at 20:47

1

In the code snippet below, I used f-string style to format the query string

[...]
query = f"SELECT * FROM `table` WHERE `Code` = 'RM' AND LEFT(`Date`, 2) = '{month}' AND RIGHT(`Date`, 4) = '{year}'"
cursor.execute(query)
[...]

answered Dec 08 '20 at 20:47

Alexis.Rolland

5,724
6
50
77

Worked like a charm! Thanks! – TxFirePiper Dec 08 '20 at 21:49
This answer is vulnerable to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection) unless the interpolated variables are generated completely internally (and even still using a parameterized query would be better) – Henry Woody May 21 '22 at 01:18

score 0 · Answer 2 · answered Dec 08 '20 at 20:54

0

try with this:

query = "SELECT * 'table' WHERE 'Code' = 'RM' AND 'Date' LIKE '%{0}_{1}'".format(month, year)
cursor.execute(query)

In this way, 'query' variable value will be:

"SELECT * FROM 'table' WHERE 'Code' = 'RM' AND 'Date' LIKE '%05_1999'"

For more information about string formatting, let's have a look to Python String Formatting Best Practices - Real Python

answered Dec 08 '20 at 20:54

Stefano Paviot

130
1
6

This answer is vulnerable to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection) unless the interpolated variables are generated completely internally (and even still using a parameterized query would be better) – Henry Woody May 21 '22 at 01:18

Passing variables into a MySQL query using python

2 Answers2