CODEWITHSUNDEEP
pythonmysqlpython-3.xpymysql
TxFirePiper
TxFirePiper

Reputation: 45

Passing variables into a MySQL query using python

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!

Upvotes: 1

Views: 3469

Answers (2)

Stefano Paviot
Stefano Paviot

Reputation: 128

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

Upvotes: 0

Alexis.Rolland
Alexis.Rolland

Reputation: 6361

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)
[...]

Upvotes: 0

Related Questions