Questions tagged [psycopg2]

1

votes
1

answer
21

Views

psycopg2 syntax error because of single quotes in insert command

I'm trying to execute an 'insert table' command using psycopg2. I create the command string in a function which returns a string of the following form: create table %s ( %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , %s %s , primary key %s ); The function also retur...
eager2learn
0

votes
0

answer
5

Views

No module named psycopg2._psycopg2 on Google Cloud Functions

I have a Google Cloud Function that uses Google Cloud SQL through psycopg2. I have psycopg2 in my requirements.txt but once the service updates I get the error No module named psycopg2._psycopg2. Is this a problem with psycopg2's C libraries running in the Cloud Function's environment? How do I fix...
woodpav
1

votes
1

answer
519

Views

SQL formatting examples for long queries in python for psycopg2 connections

Typically I use Pandas to look at and play with data, it's what I'm familiar with. If I'm pulling data from a postgres database using the psycopg2 module I'm putting the SQL queries in a python file, but if it's a complicated query it extends on one line well off the screen. Is there a way to get...
hselbie
1

votes
2

answer
1.1k

Views

Python Program Won't Run - psycopg2 rename warning

I am on a mac, using vagrant in the terminal. I am trying to run a program in python that uses psycopg2. I kept getting an error that psychopg2 module didn't exist when I would run 'python3 sample.py'. So I ran 'pip3 install psycopg2'. Now I get the error below and despite reading documentation in m...
rabowlen
1

votes
2

answer
24

Views

Why psycopg2 quoted in a Pipfile

my Pipfile looks like [[source]] url = 'https://pypi.python.org/simple' verify_ssl = true name = 'pypi' [packages] Django = '*' gunicorn = '*' 'psycopg2' = '*' [requires] # our Dockerfile is based on Python 3.7 python_version = '3.7' it's working well but I have a question concerning the double quo...
Dotty
1

votes
1

answer
829

Views

Trying to connect to database using Postgres but it isn't able to translate the address on Amazon server to Amazon database

Getting this error when trying to connect to a database on Amazon. This is from an Amazon server. psycopg2.OperationalError: could not translate host name 'domain-stg-postgres.caxdkvuertc9.us-west-1.rds.amazonaws.com/projectname_dev' to address: Name or service not known I'm setting this here: db['h...
Andrew Alexander
1

votes
2

answer
667

Views

ModuleNotFoundError: No module named 'psycopg2'

I'm using Mac os. I installed pyscopg2 successfully (pip3 install psycopg2) But when I try to import psycopg2 I get the following message: Traceback (most recent call last): File '', line 1, in ModuleNotFoundError: No module named 'psycopg2'
1

votes
1

answer
691

Views

multi thread python psycopg2

I'm using multi thread inside my program in python. I've got 3 queues. In one of them I'm inserting data to postgres database. But before, I need to check if in the database already exists row with specific domain name. So I've got: class AnotherThread(threading.Thread): def __init__(self, another_q...
Daniel Koczuła
1

votes
0

answer
75

Views

Forcing datetime unaware timestamps in/out of DB queries (Javascript, Flask, Postgres, stack)

I'm working on a webapp that has a Javascript front-end that talks JSON with a python (flask) middle and a postgres backend. I'd like to safely represent timestamps without loss, ambiguity or bugs. Python itself doesn't generate any timestamps, but it translates them between the client and the DB, w...
user48956
1

votes
2

answer
197

Views

Passing variable database name in psycopg2's execute()

This is what I have: db_name = 'temp_test_database' conn.set_isolation_level(0) cursor.execute('DROP DATABASE IF EXISTS {}'.format(db_name)) This functionality works and does what I expect, but violates the parameter passing rule. I have looked at SQL string composition documentation, but this doesn...
jholland
1

votes
0

answer
95

Views

Unable to upload section of pandas dataframe using one psycopg2 sql query to postgresql

I have a pandas dataframe that I wish to upload to a PostgreSQL 10.2 database. The dataframe looks as so: c1 c2 c3 c4 c5 c6 c7 c8 c9 text text integer integer integer integer integer text geog I am trying to upload the dataframe using psycopg2, in essence upload the first 8...
Kieran
1

votes
0

answer
242

Views

ImportError: No module named psycopg2._psycopg with google cloud sdk dev_appserver.py script

I am following the google cloud intro for python apps. I am using a simple flask app similar to the example in the page with the addition of using the psycopg2 python library for postgres. When run under the google SDK dev_appserver.py script, the stack below is thrown. I believe that what is happen...
Grant McKenzie
1

votes
0

answer
361

Views

psycopg2 - Unkeyed Connection

I'm trying to concurrently insert items into a postgres table via ThreadedConnectionPool, but I keep getting psycopg2.pool.PoolError: trying to put unkeyed connection - not sure why this is happening. I've tried running it sequentially also but still getting the same error. Essentially the code scr...
JC1
1

votes
1

answer
124

Views

Psycopg2 DictCursor returns nothing

My query is: query = ''' SELECT * from table where id IN (SELECT ext_id from table2 where title= %s and year = %s) ''' Next i use cursor = conn.cursor(cursor_factory=pg.extras.DictCursor) cursor.execute(query, (title, year)) result = cursor.fetchall() It returns [], i.e. nothing But if i set cursor...
Kanarsky
1

votes
0

answer
71

Views

AssertRaises Doesn't Work with PsycoPg2

I am trying to test that an error is thrown in unittest with Python 3.6 when working with psycopg2. In this case, the database named foo does not existm, and it should throw an OperationalError because the database is not a valid one. Here is a sample test case: # -*- coding: utf-8 -*- import unitte...
unseen_damage
1

votes
1

answer
172

Views

psycopg2: Error inserting with foreign keys

I am trying to first insert a line on a table 'lf_calculator_user' then get the id of the line I just inserted and use that id to insert it as a foreign key into another record on a the table 'lf_calculator_request'. Here is my code: if event['email'] is not None: cursor.execute('INSERT INTO lf_calc...
Rémi
1

votes
0

answer
122

Views

How to speed up join query in postgresql

I use psycopg2 to to join two tables together. But for some reason the process is taking way too long. Both tables have index. The tables have around 280 rows and 5-6 columns. But they do have a geometry column with complex boundaries of countries. This is how the query looks like: sqlstr = '''CREAT...
user1919
1

votes
0

answer
292

Views

Psycopg2 not raising no results to fetch error

I have the following code: def execute_statement(stmt): #create connection conn = psdb.connect(dbname='db', user='user', host='localhost', password='password') cur = conn.cursor() cur.execute(stmt) rows=cur.fetchall()[0] conn.close() return rows def get_product_details(request): '''Retrieve all info...
Nathan Ruth
1

votes
1

answer
636

Views

AWS Lambda Python/Boto3/psycopg2 Redshift temporary credentials

I'm pretty new to AWS so please let me know if what I'm trying to do is not a good idea, but the basic gist of it is that I have a Redshift cluster that I want to be able to query from Lambda (Python) using a combination of psycopg2 and boto3. I have assigned the Lambda function a role that allows i...
GSkurski
1

votes
1

answer
128

Views

Python .format adding extra quotes to string when using psycopg2 [closed]

Hope someone can help as i'm getting myself tied in knots here. Trying to copy a table and output it as a CSV using python, psycopg2 and postgresql. I need to have the table name and output file name as variables because I will be later changing tablename to an array so it can loop through multiple...
Jonnyboy
1

votes
0

answer
32

Views

psycopg2: execute insert statement without fetching all data back

After connecting to a database, I execute a query which will return too many rows so I fetch only 100000 rows at a time. I calculate some values from the fetched data and want to insert them into another table. Can I use the cursor for the former select statement? Will I be able to fetch remaining r...
user547960
1

votes
2

answer
1.4k

Views

Python SQLAlchemy: psycopg2.ProgrammingError relation already exists?

I have repeatable tried to create a table MYTABLENAME with SQLAlchemy in Python. I deleted all tables through my SQL client Dbeaver but I am getting an error that the table exists such that Traceback (most recent call last): File '/home/hhh/anaconda3/lib/python3.6/site-packages/sqlalchemy/engine/bas...
hhh
0

votes
0

answer
6

Views

Handling errors produced by incorrect queries using psycopg2 and pandas

I have a function that queries a postgres server using psycopg2 and returns a pandas dataframe back. There are two major issues that can cause the function to fail. 1) the server is down - This gets handled by a psycopg2.OperationalError exception. Which I can use to make the user aware that they sh...
DudeWah
1

votes
0

answer
419

Views

psycopg2.DataError: integer out of range

Thanks in advance for the help. I am new to Django, and trying to use postgres for my database. When I try to migrate my Django models, the following error shows up: psycopg2.DataError on line 85 of django/db/backends/utils.py django.db.utils.DataError I originally was using MariaDB and there were...
vaibhav
1

votes
0

answer
334

Views

psycopg2: TypeError: Expected bytes or unicode string, got quoted_name instead

I am trying to load a ~900 MB file to a Postgres database, however the copy_from method is causing some issues (the df.to_sql method was taking over 8 hours to load this data to AWS). For example here is some code that converts a dataframe to stringio sio = StringIO() sio.write(raw_data.to_csv(inde...
spitfiredd
1

votes
1

answer
478

Views

psycopg2.extras.DictCursor not returning dict in postgres

Im using psycopg2 to access postgres database using the below query. In order to return a dictionary from the executed query, im using DictCursor in my cursor but still my output is a list and not a dictonary. Here is the program and output below. import psycopg2.extras try: conn = psycopg2.connect(...
user1050619
1

votes
1

answer
336

Views

Ponyorm with psycopg2: server closed the connection unexpectedly (session timeout?)

I'm using the standard pony 'architecture' as in the examples. If I do the following steps, the problem occurs: Start application. I can work with the database properly I can do stress tests all night, everything goes well If I don't do anything for like 10-15 minutes and then I try to execute somet...
Dániel Kovács
1

votes
1

answer
93

Views

How to use python loop to through file and execute queries using parameter from text file

I'm trying to get a script working that takes each line from a file and use the line as input to run the SQL query. Specifically I'm trying to use a file that has a list of domains and use those domains names to query a postgresql database. Any help would be greatly appreciated! from __future__ impo...
bedford
1

votes
1

answer
170

Views

Unable to read column types from amazon redshift using psycopg2

I'm trying to access the types of columns in a table in redshift using psycopg2. I'm doing this by running a simple query on pg_table_def like as follows: SELECT * FROM pg_table_def; This returns the traceback: psycopg2.NotSupportedError: Column 'schemaname' has unsupported type 'name' So it seems l...
Zachary Zipper
1

votes
0

answer
596

Views

psycopg2 could not translate host name

My airflow server periodically fails. When I check the gunicorn logs, the error before all works shutting down looks like this: OperationalError: (psycopg2.OperationalError) could not translate host name 'my-airflow-db.l9zijaslosu.us-east-1.rds.amazonaws.com' to address: Name or service not known (B...
Brett
1

votes
2

answer
61

Views

Is this a bug in psycopg2 cur.mogrify()?

I think that cur.mogrify() is improperly injecting some 's into the string it generates. I'm trying to write a tiny little ORM for personal use but I ran into this behavior that seems like it could be a bug: class BaseModel(): def __init__(self): self.id = None @classmethod def find(cls, id): sql...
H. Haller
1

votes
1

answer
36

Views

psycopg2 rounds my float in SELECT statement

I'm writing a quick script to retrieve values from my database using psycopg2. Everything seemed to work fine until I verified the values in my output. I was astonished to realise they were rounded up. I double-checked the values stored and they are correct. So why it this query cursor = connection....
Julien Renggli
1

votes
0

answer
157

Views

Are sqlalchemy engine objects disposed automatically?

I have been searching a lot around sqlalchemy connections and engine configuration after I got one of the error: psycopg2.OperationalError) FATAL: remaining connection slots are reserved for non-replication superuser connections I know it is related is pool_size and have increased it for the applic...
mad_
1

votes
1

answer
202

Views

psycopg2.ProgrammingError: syntax error at or near “select”

I have a python script, which read a sql file and execute the sql command stored in it. But when executing it I got below error: psycopg2.ProgrammingError: syntax error at or near 'select' LINE 1: select * from image the sql file content is: select * from image which is simple and should be correct...
ZhaoGang
1

votes
1

answer
61

Views

Cast a PostgreSQL row to text, but keep columns separate

I am working on a Postgres viewer using Python. I need to convert all columns to text so I can display them in my GUI. I don't know how many columns there are per table or the names of them as this is supposed to be a generic viewer. Google got me this code: SELECT t.*::text FROM table AS t; However...
theGtknerd
1

votes
1

answer
126

Views

How to convert all the memoryview columns to bytes columns in a Pandas dataframe?

I'm retrieving a large amount of data from PostgreSQL with: it = pandas.read_sql_table(table, DB_CONN, chunksize=1000) But Pandas uses the psycopg2 adapter for PostgreSQL, which returns a memoryview instead of bytes for historical reasons. To my knowledge, there is no option to make psycopg2 return...
Antoine Pietri
1

votes
1

answer
133

Views

psycopg2: What page_size to use

I'm writing a large number of records to a postgres database, using psycopg2.extras.execute_values(cursor, query, data, page_size=100) I get what the page_size parameter does, but don't really know what would be a sensible value to set it to. (Above uses the default value of 100.) What are the downs...
acdr
1

votes
0

answer
103

Views

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Series'

I am trying to insert rows into a Postgres database from a pandas dataframe. I am packaging up the dataframe as follows: records = records.apply(lambda row: records(**row), axis=1) Then committing as follows: for record in records: if not records(database.config.db_session, record.id_coda): databa...
avocet
1

votes
0

answer
26

Views

How do I request the table size from psycopg2?

Experimentally, the following works: from psycopg2 import sql def table_size(conn, table): with conn.cursor() as cu: cu.execute(sql.SQL('SELECT pg_table_size({t}), pg_indexes_size({t});').format(t=sql.Literal(table))) return cu.fetchall()[0] However, I wonder if I am using sql.SQL/sql.Literal correc...
sds
1

votes
1

answer
67

Views

Psycopg2 - How to include 'Null' values in inserting dictionary of list to table in postgres?

I'm parsing my xml files and store them to a dictionary of list, where I will insert them into table at posgres using psycopg2. However, not all rows got inserted in the tables (it only inserted to the lowest number of values in the list). Here is the snippet of the dictionary of list: dict_songs =...
LOR121

View additional questions