pg8000 | A Pure-Python PostgreSQL Driver | Database library

I am a newbie to web development. My task is to download a large amount of data from a web api, and to upload them into a postgres database on google cloud.

Because the amount of data is very large, I have a for-loop that scrapes the data part-by-part, insert each part into the cloud table, and finally commits everything. I use sqlalchemy and pg8000 to do the job.

Here is the basic structure of my code:

I am attempting to execute a raw sql insert statement in Sqlalchemy, SQL Alchemy throws no errors when the constructed insert statement is executed but the lines do not appear in the database.

As far as I can tell, it isn't a syntax error (see no 2), it isn't an engine error as the ORM can execute an equivalent write properly (see no 1), it's finding the table it's supposed to write too (see no 3). I think it's a problem with a transaction not being commited and have attempted to address this (see no 4) but this hasn't solved the issue. Is it possible to create a nested transaction and what would start the 'first' so to speak?

Thankyou for any answers.

Some background:

1. I know that the ORM facilitates this and have used this feature and it works, but is too slow for our application. We decided to try using raw sql for this particular write function due to how often it's called and the ORM for everything else. An equivalent method using the ORM works perfectly, and the same engine is used for both, so it can't be an engine problem right?

2. I've issued an example of the SQL that the method using raw sql constructs to the database directly and that reads in fine, so I don't think it's a syntax error.

3. it's communicating with the database properly and can find the table as any syntax errors with table and column names throw a programmatic error so it's not just throwing stuff into the 'void' so to speak.

4. My first thought after reading around was that it was transaction error and that a transaction was being created and not closed, and so constructed the execute statement as such to ensure a transaction was properly created and commited.

   ...

I'm trying to use pip-compile to build my requirements.txt file and I get the following error.

I try to read or write from/to an AWS RDS Proxy with a postgres RDS as the endpoint. The operation works with psql but fails on the same client with pg8000 or psycopg2 as client libraries in Python.

The operation works with with pg8000 and psycopg2 if I use the RDS directly as endpoint (without the RDS proxy).

sqlaclchemy/psycopg2 error message:

Getting below error:

I am adding new column to postgresql

Json response is in following format:

I use sqlalchemy for Postgres DB.

I am trying to reuse a code which I copied from https://www.opsguru.io/post/solution-walkthrough-visualizing-daily-cloud-spend-on-gcp-using-gke-dataflow-bigquery-and-grafana. Am not too familiar with python as such seek for help here. Trying to copy GCP Bigquery data into Postgres

I have done some modification to the code and am getting some error due to my mistake or code

Here is what I have

I have been working with python and postgreql for over a year. I can connect and work with postgres databases by blindly using various libraries. But whenever I change platform (most recently from macOS laptop to remote ubuntu server) I go through a day or so of trying to get libraries working eg. I was using 'pyodbc' in some modules but when I migrated the code to the server I had to switch to 'pg8000' because the modules as they were kept throwing errors.

Can someone explain or point me to a link explaining how python connects to dB's? For example, why do I need a MS ODBC driver for 'pyodbc' to connect to an Azure SQL or postgresql but 'pg8000' seems to need nothing at all to connect to a postgresql? When I move to an Ubuntu environment and install ODBC drivers they show up on root under /etc, and /opt (for MS ODBC) but also in my Conda environment (/anaconda3/envs/) and I don't know which is the correct choice for 'ODBC.ini'?

Like I say, I can get things working but really have no understanding as to why they are working and that means I waste time experimenting every time I deal with a change in environment. I've not yet found an explanation online that covers more than a very specific circumstance eg. 'here's how to install our driver ...' Any help would be appreciated.

Final Update:

Following the responses esp. @Thompson the diagram below seems to be the final interpretation and I have a better idea of where to look for answers. For the record pyodbc, SQLAlchemy and pg8000 have been my tools of choice with no problems except as described in the question.