Questions tagged [psycopg2]
1091 questions
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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(...
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...
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...
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...
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...
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...
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....
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...
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...
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...
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...
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...
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...
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...
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 =...