MySql read_sql python query with variable @

Question

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 ?

PyPingu · Accepted Answer · 2018-01-03 18:01:38Z

1

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’}

edited Jan 3, 2018 at 18:01

answered Jan 3, 2018 at 16:55

PyPingu

1,7471 gold badge10 silver badges23 bronze badges

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

11 Comments

Kenny Over a year ago

Thanks for your response. You can try setting @null_val to some other string, the same error will occur. I just gave an example. Secondly, as indicated, I would love for the query string to be least modified as possible, as the query is quite lengthy; the more modif I have on the python version, the more modif I have to do every time the original query changes and I paste it to python. It seems your solution belongs to the class of using %s. Finally, could you elaborate more on 'sanitizing param' ?

PyPingu Over a year ago

It would help if you could explain a bit why you don't want to change the query? Doing it using paramatization in python only involves making a single query and you need it in string format to use it with read_sql anyway. As for sanitizing paramaters, if a parameter is being supplied by an external user then that user can supply some SQL which, without sanitizing, could perform queries such as 'DROP TABLE'. I suggest you google around 'SQL injection' to better understand the risks.

Kenny Over a year ago

For simplicity and keeping complexity away for people maintaining it. I will not always be the one supporting it. When a person modifies the query in Mysql and tested to get what he wants, he can copy paste into python without upraising the whole world 'oh you modified something ? Here's the step you need to do in python : 1/ copy paste. 2/ @null_value replaced by {s} 3/ count the number of your {s} then create a tuple of same size, etc. I am aware of SQL injection. I was just wandering what 'sanitizing param' means

PyPingu Over a year ago

See my edit above. Abusing read_sql to do something it's not intended for is not exactly good for simplicity. You don't need a tuple of the length using either the methods I gave above. "Your long query".format(c=value) is clear, easy, and works with read_sql. As for copying from MySQL it would be an easy find/replace of @my_const for {c}.

PyPingu Over a year ago

Triple-quoting (either using " or ') is a special case in Python which is used for multi-line strings. Using ''' or just ' in this case are basically identical. If you want to escape quotes in a string use the opposite quoting style for the whole string (i.e. use double quotes inside single quotes or vice versa) OR you have to use \ to escape the.

|

Collectives™ on Stack Overflow

MySql read_sql python query with variable @

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related