Is there a way to use read_sql_query and the query has WHERE column = Variable?

Question

I want to create a dataframe that reads data from another table that constantly updates and select only the rows which contain a product code equal to the one entered by a user

I've already tried

pd.read_sql_query

to create the dataframe

This is the code I've tried and it treated it as a sqlalchemy syntax but I don't know why

product = pd.read_sql_query("SELECT * FROM StoringTF WHERE Product_Code = ?",(Val), c)

where Val is a one item tuple that stores the value of product code entered by user and StoringTF is the main table which I'm trying to extract data from.

I hope you get the idea of what I want to do because there's something wrong with the syntax and that thing makes python expect it to be sqlalchemy. Thanks and hope you can help.

score 1 · Answer 1 · answered Oct 17 '19 at 14:52

1

With pyformat you can write it like this

Val = 'abc'
product = pd.read_sql_query(
    "SELECT * FROM StoringTF WHERE Product_Code = %(val)s",
    c, params={'val': Val}
)

Formating the string in python, as suggested in some answers, opens up a chance for an SQL injection or just errors caused by some special characters in the variable.

answered Oct 17 '19 at 14:52

Heikki Pulkkinen

267
2
8

Thank you very much for your contribution. I really appreciate it.But I'm facing another problem. The program itself works fantastically. but when I try to call it from another python file it shows some errors quickly in the terminal and closes quickly. Did you encounter that before? Do you know how to fix it? – Multimedica Sco Oct 17 '19 at 15:36
What kind of error message you get? Could you also give an example on what is stored in variable Val? – Heikki Pulkkinen Oct 25 '19 at 11:04

score 0 · Answer 2 · answered Oct 17 '19 at 14:34

0

You can do :

product = pd.read_sql_query("SELECT * FROM StoringTF WHERE Product_Code = %s" % str(your_variable), c)

answered Oct 17 '19 at 14:34

pymym213

321
3
10

score 0 · Answer 3 · answered Oct 17 '19 at 14:41

0

You can try this

product = pd.read_sql_query("SELECT * FROM StoringTF WHERE Product_Code = {}".format(Val), c)

answered Oct 17 '19 at 14:41

Bishnu

99
1
7

score 0 · Answer 4 · answered Oct 17 '19 at 17:41

Your original code had a mistake, namely that (Val) is not a 1-element tuple.

You can look at How to create a tuple with only one element and the Python documentation for more details.

Your original code should be fine with an additional comma, that is:

product = pd.read_sql_query("SELECT * FROM StoringTF WHERE Product_Code = ?",(Val,), c)

score -1 · Accepted Answer · answered Oct 17 '19 at 14:47

-1

if you are using python 3.6 or higher the most efficient way to manipulate strings are so named "f-strings", it is much faster than every other way:

product = pd.read_sql_query(f"SELECT * FROM StoringTF WHERE Product_Code = {Val}", c)

answered Oct 17 '19 at 14:47

Sofien

478
3
12

Thanks a lot. It fixed it. But I'm facing another problem. The program itself works fantastically. but when I try to call it from another python file it shows some errors quickly in the terminal and closes quickly. Did you encounter that before? Do you know how to fix it – Multimedica Sco Oct 17 '19 at 15:32
1

Please do not encourage methods of building SQL queries that are likely to result in SQL injections. – Jean Hominal Oct 17 '19 at 17:34
Try to open new terminal and open the start the process from there, then the error will be displayed after running the file and the terminal won't close. – Sofien Oct 17 '19 at 22:09

Is there a way to use read_sql_query and the query has WHERE column = Variable?

5 Answers5

Linked

Related