CODEWITHSUNDEEP
pythonsqlpandas
Ian Laird
Ian Laird

Reputation: 17

Error passing pandas query to sql database

I have a simple sql query which executes directly in sql server studio

select latitude from locations.dbo.users WHERE firstname='Mike'

This same query can be perfored fine using pandas module in python

pandas.read_sql_query('select latitude from locations.dbo.users WHERE firstname=\'Mike\'',conn)

but when trying to query the firstname column with a variable i get an error like so..

firstname="Mike"
pandas.read_sql_query('select latitude from locations.dbo.users WHERE firstname='+firstname, conn)

'Execution failed on sql 'select latitude from locations.dbo.users WHERE firstname=Mike': ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Mike'. (207) (SQLExecDirectW)")'

I know the error is simply down to the placement of my quotes and layout of the string but can't seem to get the right layout for the query to succeed exactly as it would if i did it without the variable. Have tried all manner and placements of double and single quotes etc.

Upvotes: 0

Views: 340

Answers (1)

Alessandro Parolin
Alessandro Parolin

Reputation: 146

You are missing the single quotes in your concatenated string. The following should work:

pandas.read_sql_query("select latitude from locations.dbo.users WHERE firstname='"+firstname+"'", conn)

Alternatively:

pandas.read_sql_query('select latitude from locations.dbo.users WHERE firstname=\''+firstname+'\'', conn)

Upvotes: 1

Related Questions