I'm trying to create a query using Pycopg2's SQL String Composition which in I need to use a curly brackets inside my query to update a key value in a jsonb column. Something like this: update myschema.users set data =…
I need to update the row with the CSV filename and the time data was inserted into the database. I can use the below code to insert data from CSV into the database: with open('my.csv', 'r') as f: next(f) cur.copy_from(f,…
Using psycopg2, I could write large results as CSV using copy_expert and a BytesIO buffer like this with pandas: copy_sql = "COPY (SELECT * FROM big_table) TO STDOUT CSV" buffer = BytesIO() cursor.copy_expert(copy_sql, buffer, size=8192) buffer.seek(0) pd.read_csv(buffer, engine="c").to_excel(self.output_file) However, I…
I have a try and except block where I would like to catch only the errors in the psycopg2.errors and not any other error. The explicit way would be: try: # execute a query cur = connection.cursor() cur.execute(sql_query) except psycopg2.errors.SyntaxError,…
On a virtual machine I have 2 docker containers running with the names <postgres> and <system> that run on the network with name <network>. I can't change options in these containers. I have created a flask application that connects to…
I have a project with 10+ parsers and at the end have this code: ` cursor = conn.cursor() my_file = open(r'csvfile.csv') sql_statement = """ CREATE TEMP TABLE temp ( LIKE vhcl ) ON COMMIT DROP; COPY temp FROM STDIN WITH…
I want to 'group by' beers so that they are grouped together, with the respect aggregate rating and tasters (people who review the beers) listed in separate columns. Here is my code: create or replace view tasters_avg_ratings1 as select a.taster…
I'm using psycopg2 library to connection to my postgresql database. Every time I want to execute any query, I make a make a new connection like this: import psycopg2 def run_query(query): with psycopg2.connect("dbname=test user=postgres") as connection: cursor = connection.cursor() cursor.execute(query)…
I need to insert multiple values into a table after checking if it doesn't exist using psycopg2. The query am using: WITH data(name,proj_id) as ( VALUES ('hello',123),('hey',123) ) INSERT INTO keywords(name,proj_id) SELECT d.name,d.proj_id FROM data d WHERE NOT EXISTS (SELECT…
I seem to have relatively easy question, but I have a little problem. I would like to iterr through the column prices in table products and then sum the prices. I know an easy solution would be to change sql…