'h' format requires -32768

Question

I am trying to write a dataframe into postgreSQL database table. When i write it into heroku's postgres SQL database, everything works fine. No problems. For heroku postgresql, I use the connection string

    connection_string = "postgresql+psycopg2://%s:%s@%s/%s" % (
        conn_params['user'],
        conn_params['password'],
        conn_params['host'],
        conn_params['dbname'])

However, when i try to write the dataframe into GCP's cloud sql table, i get the following error...

struct.error: 'h' format requires -32768 <= number <= 32767

The connection string i use for gcp cloud sql is as follows.

    connection_string = \
        f"postgresql+pg8000://{conn_params['user']}:{conn_params['password']}@{conn_params['host']}/{conn_params['dbname']}"

the command i use to write to the database is the same for both gcp and heroku

df_Output.to_sql(sql_tablename, con=conn, schema='public', index=False, if_exists=if_exists, method='multi')

user_16608627 · Answer

I'd recommend using the Cloud SQL Python Connector to manage your connections and take care of the connection string for you. It supports the pg8000 driver and should help resolve your troubles.

from google.cloud.sql.connector import connector
import sqlalchemy

# configure Cloud SQL Python Connector properties
def getconn():
    conn = connector.connect(
        "project:region:instance",
        "pg8000",
        user="YOUR_USER",
        password="YOUR_PASSWORD",
        db="YOUR_DB"
    )
    return conn

# create connection pool to re-use connections
pool = sqlalchemy.create_engine(
    "postgresql+pg8000://",
    creator=getconn,
)

# query or insert into Cloud SQL database
with pool.connect() as db_conn:
    # query database
    result = db_conn.execute("SELECT * from my_table").fetchall()

    # Do something with the results
    for row in result:
        print(row)

For more detailed examples refer to the README of the repository.

'h' format requires -32768 <= number <= 32767

1 Answers