0

I have credentials to an online postgresql server defined as following:

database=db, host_db=url_to_server, db_port=5432, db_user=username, db_password=pwd,

I want to connect to the server to read and write data using pandas/geopandas in python. The main libraries I used are psycopg, alchemy, pandas and geopandas.

I start with:

con =  psycopg.connect(dbname=db, user=db_user, password=db_password, host=host_db)
sql = 'select * from schema.table1'
gdf1 = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom')
gdf2 = gdf1.to_crs(3857)

up to here, with my credentials, the code run and read the data correctly. Then I did a few data manipulation (like reprojection here) and wanted to create a new table in the database, so I run

gdf2.to_postgis(table_name, con=con)

It gives error ValueError: Unknown Connectable: <psycopg.Connection [IDLE] (host=host_db user=username database=db) at 0x262ba667f70>

So I tried create con with sqlalchemy.create_engine() with url created from sqlalchemy.engine.url.URL(short as URL) with parameters

params = {'drivername':'postgresql', 'host':host_db, 'port':5432, 'database': db, 'username':db_users, 'password':pwd, 'query':None}

url = str(URL(**params))
engine = create_engine(url)
gdf2.to_postgis(table_name, con=engine)

Then I got this error: OperationalError: (psycopg2.OperationalError) connection to server at host_db, port 5432 failed: Connection refused (0x0000274D/10061)Is the server running on that host and accepting TCP/IP connections? (Background on this error at: https://sqlalche.me/e/20/e3q8)

I also tried insetad of writing the geodataframe, I tried to write a dataframe with pandas with con from psycopg at the beginning:

df1 = gdf2[['id','area']]
df1.to_sql(name=table_name, con=con, schema='public')

This gave me a database error: DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': the query has 0 placeholders but 1 parameters were passed

I tried to look everywhere but couldn't find how to fix the problem. I'm sure the credentials work because I could use them to read data, and I tried to create a table with them using pgAdmin4, which also worked too. So I ran out of thoughts in finding solutions. Could anyone help?

Michelle Li
  • 11
  • 1
    Try doing `pip install sqlalchemy==1.4.46` and using psycopg2 instead of psycopg. GeoPandas may not have been updated to work with SQLAlchemy 2.0 yet. – Gord Thompson Feb 23 '23 at 21:09

1 Answers1

0

Spent another two hours trying to figure it out and finally got it. It was in creating the url where in the params while password string is stored, the URL() class didn't convert it explicitly. It save as *** in the url instead of the real password string in the url string. In this case, I hard typed the password to created the url string and it worked to connect engine. Also as Gord suggests, use psycopg2 instead of psycopg because pandas used engine from sqlalchemy, which is only compatible with psycopg2 instead of the most updated one.

Michelle Li
  • 11
  • 1
    *"It save as *** in the url instead of the real password string in the url string."* - Yes, calling `str()` on a URL object obfuscates the password so it doesn't end up in log files or error output. There was no need to call `str()` on the URL object because `create_engine()` can accept a URL object directly. In fact, if we pass a string to `create_engine()` one of the first things that it does is [parse that string into a URL object](https://github.com/sqlalchemy/sqlalchemy/blob/fc57bafbae9d67b7ce95e26c939ca957c366b0f7/lib/sqlalchemy/engine/create.py#L551). – Gord Thompson Feb 24 '23 at 00:10