psycopg2

Recreating Postgres COPY directly in Python?

I have a block of data, currently as a list of n-tuples but the format is pretty flexible, that I'd like to append to a Postgres table - in this case, each n-tuple corresponds to a row in the DB. What I had been doing up to this point is writing these all to a CSV file and then using postgres' COPY to bulk load all of this into the data...

Checking if a postgresql table exists under python (and probably Psycopg2 )

Hello By using the Psycopg2 of Python library, how can I determine if a table exists, getting a true or false boolean. ...

Postgresql querying two different databases that are having same named tables

Greetings, by using the psycopg2 library of python, I can currently connect to a DB and retrieve such like this: conn = psycopg2.connect("dbname='db1' user='postgres' host='xxxxxx' password='mypass'"); qr = conn.cursor() qr.execute("SELECT avg(pavg) FROM mytable WHERE id =5") Now beside the database named "db1", I have to query anothe...

Installing PostgreSQL and pyscopg2 on Snow Leopard

I'm still a complete beginner in the field of web development and I'm trying to set up the Django environment. I'm reading "the definitive guide to django" to start my practice. I'm running Snow Leopard (10.6.2) on a macbook 2.1GHz. It came with Python 2.6.1. Since Snow Leopard cam with Python 2.6.1 preinstalled, so I didn't do any ext...

How to specify psycopg2 parameter for an array for timestamps (datetimes)

I'd like to run a PostgreSQL query in Python using psycopg2, which filters by a column of type timestamp without timezone. I have a long list of allowed values for the timestamp (rather than a range) and psycopg2 convenient handles arrays, so I thought that this should work: SELECT somestuff FROM mytable WHERE thetimestamp = ANY (%(time...

Debugging livelock in Django/Postgresql

I run a moderately popular web app on Django with Apache2, mod_python, and PostgreSQL 8.3 with the postgresql_psycopg2 database backend. I'm experiencing occasional livelock, identifiable when an apache2 process continually consumes 99% of CPU for several minutes or more. I did an strace -ppid on the apache2 process, and found that it w...

Cancel query execution in pyscopg2

Hi, How would one go about cancelling execution of a query statement using pyscopg2 (the python Postgres driver)? As an example, let's say I have the following code: import psycopg2 cnx_string = "something_appropriate" conn = psycopg2.connect(cnx_string) cur = conn.cursor() cur.execute("long_running_query") Then I want to cancel th...

Which Postgres value should I use in Django's DATABASE_ENGINE?

It's my first time using PostgreSQL 8.4.2 with Django(I have always used MySQL or sqlite3 in the past). Which value should I use for DATABASE_ENGINE in settings.py, postgresql_psycopg2 or postgresql? How do they differ from each other? ...

Escape SQL "LIKE" value for Postgres with psycopg2

Does psycopg2 have a function for escaping the value of a LIKE operand for Postgres? For example I may want to match strings that start with the string "20% of all", so I want to write something like this: sql = '... WHERE ... LIKE %(myvalue)s' cursor.fetchall(sql, { 'myvalue': escape_sql_like('20% of all') + '%' } Is there an existi...

Returning a value with psycopg2

Ideally I'd like to be able to do something like: id_of_new_row = cursor.lastrowid() in which I get the id of the newly created or modified row. But this isn't available through psycopg2. Alternatively, I've tried this: id_of_new_row = cursor.execute('INSERT INTO this_table (value1, value2, value3) VALUES (%s, %s, %s) RETURNING id'...

Problems with Django + mod-wsgi + psycopg2

So I am trying to make Django running with mod-wsgi for the first time. I have configured Apache as shown in examples and I am pretty sure I did everything right. I did not set the PYTHON_EGG_CACHE variable so it uses the default: /var/www/.python-eggs. I have created this directory and made it writeable for user www-data. When I open ...

How to fix "can't adapt error" when saving binary data using python psycopg2

I ran across this bug three times today in one of our projects. Putting the problem and solution online for future reference. impost psycopg2 con = connect(...) def save(long_blob): cur = con.cursor() long_data = struct.unpack('<L', long_blob) cur.execute('insert into blob_records( blob_data ) values (%s)', [long_data...

Python: automated change in variable contents

Hi, I have a Python function which receives numerous variables, and builds an SQL query out of them: def myfunc(name=None, abbr=None, grade=None, ...) These values should build an SQL query. For that purpose, Those who equal None should be changed to NULL, and those who store useful values should be embraced with 's: name="'"+name+"...

InternalError: current transaction is aborted, commands ignored until end of transaction block

i am getting this error when doing database calls in a sub process using multiprocessing library. http://pastie.org/811424 InternalError: current transaction is aborted, commands ignored until end of transaction block this is to a postgres database, using psycopg2 driver in web.py. how ever if i use threading.Thread instead of multip...

Psycopg2, Postgresql, Python: Fastest way to bulk-insert

Hi, I'm looking for the most efficient way to bulk-insert some millions of tuples into a database. I'm using Python, PostgreSQL and psycopg2. I have created a long list of tulpes that should be inserted to the database, sometimes with modifiers like geometric Simplify. The naïve way to do it would be string-formatting a list of INSERT...

Importing bytea field into PostgreSQL database via psycopg2

I have a list of values as such: row = ['0x14', '0xb6', '0xa1', '0x0', '0xa1', '0x0'] I would like to insert these into a bytea field in my PostgreSQL database, using psycopg2, but I am unfamiliar with byte strings in python. What is the best way to achieve this? ...

Avoiding duplicated data in PostgreSQL database in Python

Hello, I am working on PostgreSQL and psycopg2. Trying to get feed data which is updated every after 10 mins and keep this feeds contents in PostgreSQL database.My target is to retrieve and print those data from that table. But facing problem as duplicate data is also stored in the database every time I run that script due to insertio...

Can't install psycopg2 (Python2.6 Ubuntu9.10 PostgreSQL8.4.2)

~/psycopg2-2.0.13$ python setup.py install running install running build running build_py creating build creating build/lib.linux-x86_64-2.6 creating build/lib.linux-x86_64-2.6/psycopg2 copying lib/__init__.py -> build/lib.linux-x86_64-2.6/psycopg2 copying lib/pool.py -> build/lib.linux-x86_64-2.6/psycopg2 copying lib/extensions...

python, accessing a psycopg2 form a def?

i'm trying to make a group of defs in one file so then i just can import them whenever i want to make a script in python i have tried this: def get_dblink( dbstring): """ Return a database cnx. """ global psycopg2 try cnx = psycopg2.connect( dbstring) except Exception, e: print "Unable to connect to DB. Error [%s]" % ( e,) ...

undefined symbol: PyUnicodeUCS2_Decode whilst trying to install psycopg2

I'm getting an error whilst trying to install psycopg2 on ubuntu 9.10 64 bit. The error is: >>> import psycopg2 Traceback (most recent call last): File "<stdin>", line 1, in <module> File "psycopg2/__init__.py", line 69, in <module> from _psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID ImportError: psycopg2/_psycopg.so: ...