Why pandas.read_sql returns an empty DataFrame?

Question

I'm trying to retrieve the data from database and save into pandas.DataFrame. Here is my Python script,

conn = pyodbc.connect(sql_server)
query = '''SELECT a1, a2, a3
FROM '''  + dbschema + '''.SomeResults
WHERE FactorName = \' ''' + FactorName + ''' \' AND parametername = 'Param1' ORDER BY Factor1 '''
df = pd.read_sql(query, conn)
print(df)

However, it returns,

Empty DataFrame
Columns: [a1, a2, a3]
Index: []

I'm pretty sure it's not SQL problem, as I can retrieve the data from database using conn.cursor().

Mound

Answer 1

the reason is the way of generating that SQL:

In [307]: dbschema = 'db'

In [308]: FactorName = 'Factor1'

In [309]: query = '''SELECT a1, a2, a3
     ...: FROM '''  + dbschema + '''.SomeResults
     ...: WHERE FactorName = \' ''' + FactorName + ''' \' AND parametername = 'Param1' ORDER BY Factor1 '''

In [310]: print(query)
SELECT a1, a2, a3
FROM db.SomeResults
WHERE FactorName = ' Factor1 ' AND parametername = 'Param1' ORDER BY Factor1

# NOTE: spaces      ^       ^

You should not generate SQL this way, as it might be dangerous (read about SQL injections).

This would be a proper way:

query = """
SELECT a1, a2, a3
FROM {}.SomeResults
WHERE FactorName = ? AND parametername = 'Param1'
ORDER BY Factor1
"""

df = pd.read_sql(query.format(dbschema), conn, params=(FactorName,))

NOTE: only literals can be parameterized. I.e. we can NOT parameterized schema names, table names, column, names, etc.

Here is a funny example of a SQL injection:

Backfield

Thank you! The reason why the script didn't work is because of spaces in the query. But, thanks for pointing out the SQL injection and the funny picture (I didn't get the point of the picture though). – Mound

@Pandaaaaaaa, about the picture - it could potentially drop the whole database containing car number plates, if it's not protected against SQL injections. ;-) – Backfield