CODEWITHSUNDEEP
pythonmysqlpandasvariables
Kenny
Kenny

Reputation: 1982

MySql read_sql python query with variable @

I am aware that queries in Python can be parameterized using either ? or %s in execute query here or here

However I have some long query that would use some constant variable defined at the beginning of the query 

Set @my_const = 'xyz';
select @my_const;
-- Query that use @my_const 40 times
select ... coalesce(field1, @my_const), case(.. then @my_const)...

I would like to do the least modif possible to the query from Mysql. So that instead of modifying the query to

pd.read_sql(select ... coalesce(field1, %s), case(.. then %s)... , [my_const, my_const, my_const, ..]

,I could write something along the line of the initial query. Upon trying the following, however, I am getting a TypeError: 'NoneType' object is not iterable

query_str = "Set @null_val = \'\'; "\
    " select @null_val"
erpur_df = pd.read_sql(query_str, con = db)

Any idea how to use the original variable defined in Mysql query ?

Upvotes: 1

Views: 1426

Answers (1)

PyPingu
PyPingu

Reputation: 1747

The reason 

query_str = "Set @null_val = \'\'; "\
    " select @null_val"
erpur_df = pd.read_sql(query_str, con = db)

throws that exception is because all you are doing is setting null_value to '' and then selecting that '' - what exactly would you have expected that to give you? EDIT read_sql only seems to execute one query at a time, and as the first query returns no rows it results in that exception. If you split them in to two calls to read_sql then it will in fact return you the value of your @null value in the second call. Due to this behaviour read_sql is clearly not a good way to do this. I strongly suggest you use one of my suggestions below.

Why are you wanting to set the variable in the SQL using '@' anyway?

You could try using the .format style of string formatting.

Like so:

query_str = "select ... coalesce(field1, {c}), case(.. then {c})...".format(c=my_const)
pd.read_sql(query_str)

Just remember that if you do it this way and your my_const is a user input then you will need to sanitize it manually to prevent SQL injection.

Another possibility is using a dict of params like so:

query_str = "select ... coalesce(field1, %(my_const)s, case(.. then %(my_const)s)..."
pd.read_sql(query_str, params={'my_const': const_value})

However this is dependent on which database driver you use.

From the pandas.read_sql docs:

Check your database driver documentation for which of the five syntax styles, described in PEP 249’s paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}

Upvotes: 1

Related Questions