1

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 ?

Kenny
  • 1,902
  • 6
  • 32
  • 61

1 Answers1

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

PyPingu
  • 1,697
  • 1
  • 8
  • 21
  • 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' ? – Kenny Jan 03 '18 at 17:13
  • 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. – PyPingu Jan 03 '18 at 17:21
  • 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 – Kenny Jan 03 '18 at 17:49
  • 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 Jan 03 '18 at 18:09
  • Thank you. I'll take your advice. Nevertheless upon trying I came across 2 issues. Consider query_str = " select * from my_table a where my_column like {v_like} ".format(v_like=my_var) 1. try with my_var = '%rie' or '''%rie''' (3 single quotes) or " '%rie' ", only in the last one will my quote be kept and not second. Is it normal ? 2. With 3rd option I get my query well as select * from my_table a where my_column like '%rie' . Yet read_sql failed with "not all arguments converted during string formatting".Apparently it thinks %rie is another argument. Escaping with '%%rie' also fail.Any idea – Kenny Jan 03 '18 at 18:22
  • Please ignore my 2nd point. I found the error. However still wondering about the 1st point where escaping with ''' does not work – Kenny Jan 03 '18 at 18:30
  • Yes, that is expected string quoting behaviour in Python. – PyPingu Jan 03 '18 at 18:35
  • I meant, even after escaping ''' (3 single quotes), the single quote is lost – Kenny Jan 04 '18 at 11:09
  • How exactly are you typing it out with triple quotes? If you are doing '''%rie''' then of course no quotes are included. If you are doing ''''%rie'''' then you'll get a SyntaxError, if you do '''"%rie"''' or """'%rie'""" then you'll get either a double quoted or single-quoted %rie inserted in to the string – PyPingu Jan 04 '18 at 11:28
  • Correct me if I am wrong. ' ' is used to escape ' in python string. And yet my_var = '''%rie''' query_str = " select * from my_table a where my_column like {v_like} ".format(v_like=my_var) or my_var = '%rie' query_str = " select * from my_table a where my_column like {v_like} ".format(v_like=my_var) produce the same thing, without quote around %rie – Kenny Jan 04 '18 at 11:34
  • 1
    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. – PyPingu Jan 04 '18 at 11:39