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 — Henry
– Henry, Commented May 21, 2022 at 1:15

Alexis.Rolland · Accepted Answer · 2020-12-08 20:47:37Z

0

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 8, 2020 at 20:47

Alexis.Rolland

6,4217 gold badges58 silver badges84 bronze badges

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

2 Comments

TxFirePiper Over a year ago

Worked like a charm! Thanks!

Henry Over a year ago

This answer is vulnerable to SQL injection attacks unless the interpolated variables are generated completely internally (and even still using a parameterized query would be better)

Stefano Paviot · Accepted Answer · 2020-12-08 20:54:03Z

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 8, 2020 at 20:54

Stefano Paviot

1281 silver badge6 bronze badges

1 Comment

Henry Over a year ago

This answer is vulnerable to SQL injection attacks unless the interpolated variables are generated completely internally (and even still using a parameterized query would be better)

Collectives™ on Stack Overflow

Passing variables into a MySQL query using python

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related