CODEWITHSUNDEEP
pythonsqlpostgresqlpandasquotes
user2908926
user2908926

Reputation: 205

pd.read_sql_query single / double quotes formatting

I'm using Python(Jupyter Notebook) and Postgres Database and am struggling to populate a Pandas dataframe.

The sql code runs fine using the query builder in pgAdmin4 which is

SELECT "Date","Close" FROM test WHERE "Symbol" = 'AA'

However I can't get this to work in my Jupyter notebook, I assume its something to do with single quotes and double quotes but can't figure out what to change and have hit a wall. In the notebook I'm trying

df = pd.read_sql_query('SELECT "Date","Close" FROM public.test WHERE "Symbol" = AA', conn)

but don't know what quotes to use around the AA (data) part of the query, if I use double quotes pandas thinks AA is a column and if I use single quotes it breaks the string.

I'd really appreciate it if someone could point me in the right direction.

Thanks

Upvotes: 2

Views: 7035

Answers (2)

Marjan Moderc
Marjan Moderc

Reputation: 2859

This will work:

df = pd.read_sql_query("SELECT Date,Close FROM public.test WHERE Symbol = 'AA'", conn)

Sql chars must have single quotes, but column names don't need quotes at all.

If you really need double quotes inside sql query, then just make sure you use triple outer quotes on python string, like so:

df = pd.read_sql_query("""SELECT "Date" FROM public.test WHERE Symbol = 'AA'""", conn)

Upvotes: 2

Clem G.
Clem G.

Reputation: 396

You can use triple quotes if you have a huge query, you can declare it over multiple lines like this :

df = pd.read_sql_query("""SELECT "Date","Close" 
                          FROM public.test 
                          WHERE Symbol = 'AA'""", conn)

Solution from @Marjan Moderc works as well of course.

Upvotes: 2

Related Questions