Using SQL Cases with Pandas pd.read_sql?

Question

I am using pandas pd.real_sql(SQL Query, con) to read in data from a database. When I use a basic query like SELECT * FROM COLUMN WHERE ROW IS NOT NULL I get a normal result. But, I can't figure out how to use cases. When I try to use a case I always get an error ...near "CASE": syntax error

SELECT * CASE WHEN Column IS NULL THEN False ELSE True END AS TrueFalse FROM Sheet",con Anyone know how to use sql cases with pandas

The SQL looks like T-SQL (aka MS SQL Server). There's no True or False in T-SQL. You can return 1 or 0 then cast them to boolean in Python instead. — Code Different

NK09 NK09 · Accepted Answer · 2020-01-23T18:48:45

import psycopg2 as pg
import pandas.io.sql as psql
import pandas as pd

Q1 = '''
select col1, col2,
case when col1 is null THEN '1' ELSE '0' END AS col3
from table1
'''

connection = pg.connect("host='ABC' dbname=ABC user=ABC 
password='ABC'")

df = pd.read_sql_query(Q1, con=connection)

*replace ABC with your db host name, db name, user name and password

Using SQL Cases with Pandas pd.read_sql?

1 Answers