GeoPandas dataframe from PostGIS table having date column

Question

I have this PostgreSQL PostGIS database

PostGIS database

and I want to read this data in GeoPandas.

I am able to create the GeoPandas database when I exclude the columns having dates/datetime

#Reading data from postgis in geopandas 
conne = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432', database="agro_dss")
print ("connection to the database suscessfull")
#all columns except date columns 
sql = "select geometry, state_name, district_name, rainfall, temperature_max, temperature_min, humidity, humidity2, wind_speed, wind_direction, cloud_cover from forecast_data"
pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry')
pstgis_df

I have followed the steps as in GeoPandas documentation

but it is showing error

conne = psycopg2.connect(user='postgres', password='your_password', host='localhost', port= '5432', database="agro_dss")
print ("connection to the database suscessfull")
sql_base = "select geometry, rainfall from forecast_data"
pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry' , parse_dates={'Issue_date': '%Y-%m-%d'})

what would be the proper syntax for adding the columns having date in postgis table to GeoPandas dataframe?

when I am passing date column in the SQL query, its showing error. How to add that column?

• Abhilash Singh Chauhan: thanks for the suggestion, but how to fix " parse_dates={Issue_date: '%Y-%m-%d'}) " here in : pstgis_df = gpd.GeoDataFrame.from_postgis(sql, conne, geom_col='geometry' , parse_dates={Issue_date: '%Y-%m-%d'}) @BERA
• Abhilash Singh Chauhan: I don't know how to do that, i just pasted the code from my jupyter notebook, and it was showing like its showing now, that's why i have posted the snaps for reference. @BERA
• Abhilash Singh Chauhan: tried that here already i.sstatic.net/UBNcy.png @BERA
• Abhilash Singh Chauhan: sorry @BERA , forgot to put the column name in inverted-commas. Thanks, it parsed the date column, but by doing so, it is parsing the date but not showing that column in dataframe. For doing so I added the column name in sql query, and when i am doing so, it’s showing error i.sstatic.net/pqfiQ.png, even after correcting the suggestions i.sstatic.net/1f0ko.png
• JGH: Please don't post pictures of error messages/code but the actual text.

Answer 1

Postgres is case sensitive. The error message tells you that issue_date is not found, and goes as far as suggesting that you may be meaning Issue_date, with a capital I.

To reference an uppercase column name, it must be within double quotes

select "Issue_date" from myTable

You would have to escape it within the query string

sql_base = "select geometry, rainfall,\"Issue_date\" from forecast_data"

• Abhilash Singh Chauhan: Since I am new to stackoverflow, I am not yet acquainted with the options available here. I tried to vote as this answer is useful but it showed in the popup : Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score. @JGH, thanks for your time though.
• JGH: :-) don't worry, this aspect is indeed a bit unclear: only the user that asked a question can "accept" the answer. Anybody - including the user that asked the question - that find the answer useful can "upvote" it (or "downvote" it!), provided they have at least 15 points of reputation