Select specified data from the full query pd.read_sql_query

beginofwork 0
Viewed 16

How to select only certain rows and columns require in pd.read_sql_query?

Extract data by using pd.read_sql_query.

#Connect server
server = 'your_server'
database = 'your_database' 
username = os.environ.get('SERVER_USERNAME')
password = os.environ.get('SERVER_PWD')
conn = pyodbc.connect('DRIVER={your_driver};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()


df = pd.read_sql_query('''

            SELECT * FROM database 

            ''', conn)

So, will getting a 'full data' fro the above query.

Due to there are few conditions in dashboard, therefore, need to filter certain row and column according to each condition require in chart. 

given = '2022-01-05'
given = datetime.strptime(given, '%Y-%m-%d')

df['Date'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.date


dff1 = df[df['Date'] == given]

df2 = dff1.groupby(['Product'])['Sale'].agg(['sum']).reset_index().rename(columns={'sum':'Total_Tx_Amount'})

print(df2)

Output:

Empty DataFrame
Columns: [Product, Total_Tx_Amount]
Index: []

sql python
1 Answers

I think your filter is wrong (no row meets the condition). That is probably because given is of type datetime and df['Date'] is of type date

Try to add .date() here:

given = datetime.strptime(given, '%Y-%m-%d').date()
bitflip0
Related