I got a lot of errors with the message :

    &quot;DatabaseError: current transaction is aborted, commands ignored until end of transaction block&quot;

 after changed from python-psycopg to python-psycopg2 as Django project&#39;s database engine.

The code remains the same, just don&#39;t know where those errors are from.

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

Looking at the output of `select * from pg_stat_activity;`, I see a column called `application_name`, described [here](http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW).

I see psql sets this value correctly (to `psql`...), but my application code (psycopg2/SQLAlchemy) leaves it blank.

I&#39;d like to set this to something useful, like `web.1`, `web.2`, etc, so I could later on correlate what I see in `pg_stat_activity` with what I see in my application logs.

I couldn&#39;t find how to set this field using SQLAlchemy (and if push comes to shove - even with raw sql; I&#39;m using PostgresSQL 9.1.7 on Heroku, if that matters).

Am I missing something obvious?

Setting application_name on Postgres/SQLAlchemy

I am writing a quick and dirty script which requires interaction with a database (PG).

The script is a pragmatic, tactical solution to an existing problem. however, I envisage that the script will evolve over time into a more &quot;refined&quot; system. Given the fact that it is currently being put together very quickly (i.e. I don&#39;t have the time to pour over huge reams of documentation), I am tempted to go the quick and dirty route, using psycopg.

The advantages for psycopg2 (as I currently understand it) is that:

1. written in C, so faster than sqlAlchemy (written in Python)?
2. No abstraction layer over the DBAPI since works with one db and one db only (implication -&gt; fast)
3. (For now), I don&#39;t need an ORM, so I can directly execute my SQL statements without having to learn a new ORM syntax (i.e. lightweight)


Disadvantages:

1. I KNOW that I will want an ORM further down the line
2. psycopg2 is (&quot;dated&quot;?) - don&#39;t know how long it will remain around for

Are my perceptions of SqlAlchemy (slow/interpreted, bloated, steep learning curve) true - IS there anyway I can use sqlAlchemy in the &quot;rough and ready&quot; way I want to use psycopg - namely:

1. execute SQL statements directly without having to mess about with the ORM layer, etc.

Any examples of doing this available?



SQLAlchemy or psycopg2?

I have a list of ids of rows to fetch from database. I&#39;m using python and psycopg2, and my problem is how to effectively pass those ids to SQL? I mean that if I know the length of that list, it is pretty easy because I can always manually or automatically add as many &quot;%s&quot; expressions into query string as needed, but here I don&#39;t know how much of them I need. It is important that I need to select that rows using  sql &quot;id IN (id1, id2, ...)&quot; statement. I know that it is possible to check the length of the list and concatenate suitable number of &quot;%s&quot; into query string, but I&#39;m afraid that it would be very slow and ugly. Does anyone have an idea on how to solve it? And please don&#39;t ask why I need to do it with &quot;IN&quot; statement - it is a benchmark which is a part of my class assignment. Thanks in advance!

Passing list of parameters to SQL in psycopg2

I&#39;m using the following to try and insert a record into a postgresql database table, but it&#39;s not working. I don&#39;t get any errors, but there are no records in the table. Do I need a commit or something? I&#39;m using the postgresql database that was installed with the Bitnami djangostack install.

    import psycopg2
    
    try:
        conn = psycopg2.connect(&quot;dbname=&#39;djangostack&#39; user=&#39;bitnami&#39; host=&#39;localhost&#39; password=&#39;password&#39;&quot;)
    except:
        print &quot;Cannot connect to db&quot;
    
    cur = conn.cursor()
    
    try:
        cur.execute(&quot;&quot;&quot;insert into cnet values (&#39;r&#39;, &#39;s&#39;, &#39;e&#39;, &#39;c&#39;, &#39;w&#39;, &#39;s&#39;, &#39;i&#39;, &#39;d&#39;, &#39;t&#39;)&quot;&quot;&quot;)
    except:
        print &quot;Cannot insert&quot;



Python psycopg2 not inserting into postgresql table

I&#39;m guessing this is a pretty basic question, but I can&#39;t figure out why:

    import psycopg2
    psycopg2.connect(&quot;postgresql://postgres:postgres@localhost/postgres&quot;)

Is giving the following error:

    psycopg2.OperationalError: missing &quot;=&quot; after
    &quot;postgresql://postgres:postgres@localhost/postgres&quot; in connection info string

Any idea? According to [the docs about connection strings](http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING) I believe it should work, however it only does like this:

    psycopg2.connect(&quot;host=localhost user=postgres password=postgres dbname=postgres&quot;)

I&#39;m using the latest psycopg2 version on Python2.7.3 on Ubuntu12.04

Connect to an URI in postgres

I&#39;ve tried to install psycopg2 (PostgreSQL Database adapater) from [this site][1], but when I try to install after I cd into the package and write 

    python setup.py install 

I get the following error: 

    Please add the directory containing pg_config to the PATH

    or specify the full executable path with the option:

    python setup.py build_ext --pg-config /path/to/pg_config build ...

    or with the pg_config option in &#39;setup.cfg&#39;.

I&#39;ve also tried &#39;sudo pip install psycopg2&#39; and I got the same message. 

After reading through the [docs][2], it asks to look at the setup.cfg file (which is below): 

    [build_ext]
    define=
    
    # PSYCOPG_DISPLAY_SIZE enable display size calculation (a little slower)
    # HAVE_PQFREEMEM should be defined on PostgreSQL &gt;= 7.4
    # PSYCOPG_DEBUG can be added to enable verbose debug information
    
    # &quot;pg_config&quot; is required to locate PostgreSQL headers and libraries needed to
    # build psycopg2. If pg_config is not in the path or is installed under a
    # different name uncomment the following option and set it to the pg_config
    # full path.
    #pg_config=
    
    # Set to 1 to use Python datetime objects for default date/time representation.
    use_pydatetime=1
    
    # If the build system does not find the mx.DateTime headers, try
    # uncommenting the following line and setting its value to the right path.
    #mx_include_dir=
    
    # For Windows only:
    # Set to 1 if the PostgreSQL library was built with OpenSSL.
    # Required to link in OpenSSL libraries and dependencies.
    have_ssl=0
    
    # Statically link against the postgresql client library.
    #static_libpq=1
    
    # Add here eventual extra libraries required to link the module.
    #libraries=


However, I&#39;m not sure if I&#39;m suppose to edit this file, since the documentation states the following: 

    then take a look at the setup.cfg file.
    
    Some of the options available in setup.cfg are also available as command line arguments of the build_ext sub-command. For instance you can specify an alternate pg_config version using:
    
    $ python setup.py build_ext --pg-config /path/to/pg_config build
    
    Use python setup.py build_ext --help to get a list of the options supported.

I&#39;ve gotten the list of options supported but I&#39;m not sure where to go from there


  [1]: http://initd.org/psycopg/
  [2]: http://initd.org/psycopg/docs/





How to install psycopg2 with pg_config error?

I want to insert a python dictionary as a json into my postgresql database (via python and psycopg2).

I have:

```python
thedictionary = {&#39;price money&#39;: &#39;$1&#39;, &#39;name&#39;: &#39;Google&#39;, &#39;color&#39;: &#39;&#39;, &#39;imgurl&#39;: &#39;http://www.google.com/images/nav_logo225.png&#39;, &#39;charateristics&#39;: &#39;No Description&#39;, &#39;store&#39;: &#39;google&#39;}

cur.execute(&quot;INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)&quot;, (1,  &#39;http://www.google.com&#39;, &#39;$20&#39;, thedictionary, &#39;red&#39;, &#39;8.5x11&#39;))
```

And it gives the error message:

&gt; cur.execute(&quot;INSERT INTO product(store_id, url, price, charecteristics, color, dimensions) VALUES (%d, %s, %s, %d, %s, %s)&quot;, (1,  &#39;http://www.google.com&#39;, &#39;$20&#39;, thedictionary, &#39;red&#39;, &#39;8.5x11&#39;))
    psycopg2.ProgrammingError: can&#39;t adapt type &#39;dict&#39;

I am not sure how to proceed from here.
I cannot find anything on the internet about how to do this exact kind of thing and I am very new to psycopg2.


psycopg2 insert python dictionary as json

Is there a good practice for entering `NULL` key values to a PostgreSQL database when a variable is `None` in Python?

Running this query:

    mycursor.execute(&#39;INSERT INTO products (user_id, city_id, product_id, quantity, price) VALUES (%i, %i, %i, %i, %f)&#39; %(user_id, city_id, product_id, quantity, price))

results in a a `TypeError` exception when `user_id` is `None`.

How can a `NULL` be inserted into the database when a value is `None`, using the `psycopg2` driver?


How to insert &#39;NULL&#39; values into PostgreSQL database using Python?

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 &quot;20% of all&quot;, so I want to write something like this:

    sql = &#39;... WHERE ... LIKE %(myvalue)s&#39;
    cursor.fetchall(sql, { &#39;myvalue&#39;: escape_sql_like(&#39;20% of all&#39;) + &#39;%&#39; }

Is there an existing **escape_sql_like** function that I could plug in here?

(Similar question to [How to quote a string value explicitly (Python DB API/Psycopg2)][1], but I couldn&#39;t find an answer there.)


  [1]: https://stackoverflow.com/questions/309945/how-to-quote-a-string-value-explicitly-python-db-api-psycopg2 &quot;my title&quot;

Escape SQL &quot;LIKE&quot; value for Postgres with psycopg2

I&#39;m trying execute a simple query, but getting this error no matter how I pass the parameters.

Here is the query (I&#39;m using Trac db object to connect to a DB):

    cursor.execute(&quot;&quot;&quot;SELECT name FROM &quot;%s&quot;.customer WHERE firm_id=&#39;%s&#39;&quot;&quot;&quot; % (schema, each[&#39;id&#39;]))

schema and each[&#39;id&#39;] both are simple strings

    print(&quot;&quot;&quot;SELECT name FROM &quot;%s&quot;.customer WHERE firm_id=&#39;%s&#39;&quot;&quot;&quot; % (schema, each[&#39;id&#39;]))

Result:
`SELECT name FROM &quot;Planing&quot;.customer WHERE firm_id=&#39;135&#39;`

There is on error is a remove quote after `firm_id=`, but that way parameter is treated a an integer and `::text` leads to the very same error.

psycopg2 TypeError: not all arguments converted during string formatting

Using psycopg2 package with python 2.7 I keep getting the titled error: psycopg2.DatabaseError: SSL SYSCALL error: EOF detected

It only occurs when I add a `WHERE column LIKE &#39;&#39;%X%&#39;&#39;` clause to my pgrouting query. An example:

    SELECT id1 as node, cost FROM PGR_Driving_Distance(
      &#39;SELECT id, source, target, cost 
         FROM edge_table
         WHERE cost IS NOT NULL and column LIKE &#39;&#39;%x%&#39;&#39; &#39;,
      1, 10, false, false)

Threads on the internet suggest it is an issue with SSL intuitively, but whenever I comment out the pattern matching side of things the query and connection to the database works fine.

This is on a local database running Xubuntu 13.10.

After further investigation: It looks like this may be cause by the pgrouting extension crashing the database because it is a bad query and their are not links which have this pattern.

Will post an answer soon ...




Postgres SSL SYSCALL error: EOF detected with python and psycopg

In one folder I have 3 files: base.py, Dockerfile and docker-compose.yml.

**base.py:**

    import psycopg2
    
    conn = psycopg2.connect(&quot;dbname=&#39;base123&#39; user=&#39;postgres&#39; host=&#39;db&#39; password=&#39;pw1234&#39;&quot;)
**Dockerfile:**
    
    FROM ubuntu:16.04

    RUN apt-get update
    RUN apt-get -y install python-pip
    RUN apt-get update
    RUN pip install --upgrade pip
    RUN pip install psycopg2-binary

    COPY base.py base.py

    RUN python base.py

**docker-compose.yml:**

    version: &#39;3&#39;
    services:
      db:
        image: &#39;postgres:latest&#39;
        expose:
          - &quot;5432&quot;
        environment:
          POSTGRES_PASSWORD: pw1234
          POSTGRES_DB: base123
      aprrka:
        build: .    
        depends_on:
          - db
After I ran `docker-compose up`, I got the following error:
 
    Traceback (most recent call last):
      File &quot;base.py&quot;, line 5, in &lt;module&gt;
    conn = psycopg2.connect(&quot;dbname=&#39;base123&#39; user=&#39;postgres&#39; host=&#39;db&#39; password=&#39;pw1234&#39;&quot;)
       File &quot;/usr/local/lib/python2.7/dist-packages/psycopg2/__init__.py&quot;, line 130, in connect
        conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
    psycopg2.OperationalError: could not translate host name &quot;db&quot; to address: Name or service not known

    ERROR: Service &#39;aprrka&#39; failed to build: The command &#39;/bin/sh -c python base.py&#39; returned a non-zero code: 1

I don&#39;t know why I have this error. I exposed port 5432. By default Compose sets up a single network for app. Each service joins the default network, I think that my app with postgres should work together. Did I write incorrect docker-compose.yml?



Could not translate host name &quot;db&quot; to address using Postgres, Docker Compose and Psycopg2

I am having trouble installing psycopg2. I get the following error when I try to `pip install psycopg2`:

    Error: pg_config executable not found.
    
    Please add the directory containing pg_config to the PATH
    
    or specify the full executable path with the option:
    
    
    
        python setup.py build_ext --pg-config /path/to/pg_config build ...
    
    
    
    or with the pg_config option in &#39;setup.cfg&#39;.
    
    ----------------------------------------
    Command python setup.py egg_info failed with error code 1 in /tmp/pip-build/psycopg2


But the problem is `pg_config` is actually in my `PATH`; it runs without any problem:

    $ which pg_config
    /usr/pgsql-9.1/bin/pg_config

I tried adding the pg_config path to the `setup.cfg` file and building it using the source files I downloaded from their website (http://initd.org/psycopg/) and I get the following error message!

    Error: Unable to find &#39;pg_config&#39; file in &#39;/usr/pgsql-9.1/bin/&#39;


But it is actually THERE!!!

I am baffled by these errors. Can anyone help please?

By the way, I `sudo` all the commands. Also I am on RHEL 5.5.

pg_config executable not found

I use SQLAlchemy and there are at least three entities: `engine`, `session` and `connection`, which have `execute` method, so if I e.g. want to select all records from `table` I can do this
    
    engine.execute(select([table])).fetchall()
    
and this

    connection.execute(select([table])).fetchall()

and even this

    session.execute(select([table])).fetchall()

\- the results will be the same.

As I understand it, if someone uses `engine.execute` it creates `connection`, opens `session` (Alchemy takes care of it for you) and executes the query. But is there a global difference between these three ways of performing such a 
task?

SQLAlchemy: engine, connection and session difference

I need to insert multiple rows with one query (number of rows is not constant), so I need to execute query like this one:

    INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

The only way I know is

    args = [(1,2), (3,4), (5,6)]
    args_str = &#39;,&#39;.join(cursor.mogrify(&quot;%s&quot;, (x, )) for x in args)
    cursor.execute(&quot;INSERT INTO t (a, b) VALUES &quot;+args_str)

but I want some simpler way.

psycopg2: insert multiple rows with one query

I would like a general way to generate column labels directly from the selected column names, and recall seeing that python&#39;s psycopg2 module supports this feature.

How do I get a list of column names from a psycopg2 cursor?

I run 
```shell
sudo pip install psycopg2
``` 
and I get a bunch of output that looks like:  

    cc -DNDEBUG -g -fwrapv -Os .....
    .....
    cc -DNDEBUG -g -fwrapv -Os .....
    .....

And at the end it says:

    ld: library not found for -lssl
    
    clang: error: linker command failed with exit code 1 (use -v to see invocation)
    
    error: command &#39;cc&#39; failed with exit status 1

    ----------------------------------------
    Cleaning up...
    Command /usr/bin/python -c &quot;import setuptools, tokenize;__file__=&#39;/private/var/folders/bz/pvj1g9xj16d10pjjgbrfl3fw0000gn/T/pip_build_root/psycopg2/setup.py&#39;;exec(compile(getattr(tokenize, &#39;open&#39;, open)(__file__).read().replace(&#39;\r\n&#39;, &#39;\n&#39;), __file__, &#39;exec&#39;))&quot; install --record /var/folders/bz/pvj1g9xj16d10pjjgbrfl3fw0000gn/T/pip-uE3thn-record/install-record.txt --single-version-externally-managed --compile failed with error code 1 in /private/var/folders/bz/pvj1g9xj16d10pjjgbrfl3fw0000gn/T/pip_build_root/psycopg2
    Storing debug log for failure in /Users/Tyler/Library/Logs/pip.log

--------

Running **easy_install** or doing it **from source** both give me the same error at the end (the part about library not found for -lssl).

--------



Running brew install (or upgrade) openssl yields the below

    $ brew upgrade openssl
    Error: openssl-1.0.1h already installed


Can anyone help me out?

error installing psycopg2, library not found for -lssl

I&#39;m using Python and psycopg2 to interface to postgres. 

When I insert a row...

    sql_string = &quot;INSERT INTO hundred (name,name_slug,status) VALUES (&quot;
    sql_string += hundred_name + &quot;, &#39;&quot; + hundred_slug + &quot;&#39;, &quot; + status + &quot;);&quot;
    cursor.execute(sql_string)

... how do I get the ID of the row I&#39;ve just inserted? Trying:

    hundred = cursor.fetchall() 

returns an error, while using `RETURNING id`:

    sql_string = &quot;INSERT INTO domes_hundred (name,name_slug,status) VALUES (&quot;
    sql_string += hundred_name + &quot;, &#39;&quot; + hundred_slug + &quot;&#39;, &quot; + status + &quot;) RETURNING id;&quot;
    hundred = cursor.execute(sql_string)

simply returns `None`. 

UPDATE: So does `currval` (even though using this command directly into postgres works): 

    sql_string = &quot;SELECT currval(pg_get_serial_sequence(&#39;hundred&#39;, &#39;id&#39;));&quot;
    hundred_id = cursor.execute(sql_string)

Can anyone advise?


thanks!

Python/postgres/psycopg2: getting ID of row just inserted

I&#39;m using *`virtualenv`* and I need to install &quot;psycopg2&quot;.

I have done the following:

    pip install http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160

And I have the following messages:

	Downloading/unpacking http://pypi.python.org/packages/source/p/psycopg2/psycopg2
	-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160
	  Downloading psycopg2-2.4.tar.gz (607Kb): 607Kb downloaded
	  Running setup.py egg_info for package from http://pypi.python.org/packages/sou
	rce/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160
		Error: pg_config executable not found.

		Please add the directory containing pg_config to the PATH
		or specify the full executable path with the option:

			python setup.py build_ext --pg-config /path/to/pg_config build ...

		or with the pg_config option in &#39;setup.cfg&#39;.
		Complete output from command python setup.py egg_info:
		running egg_info

	creating pip-egg-info\psycopg2.egg-info

	writing pip-egg-info\psycopg2.egg-info\PKG-INFO

	writing top-level names to pip-egg-info\psycopg2.egg-info\top_level.txt

	writing dependency_links to pip-egg-info\psycopg2.egg-info\dependency_links.txt

	writing manifest file &#39;pip-egg-info\psycopg2.egg-info\SOURCES.txt&#39;

	warning: manifest_maker: standard file &#39;-c&#39; not found

	Error: pg_config executable not found.



	Please add the directory containing pg_config to the PATH

	or specify the full executable path with the option:



		python setup.py build_ext --pg-config /path/to/pg_config build ...



	or with the pg_config option in &#39;setup.cfg&#39;.

	----------------------------------------
	Command python setup.py egg_info failed with error code 1
	Storing complete log in C:\Documents and Settings\anlopes\Application Data\pip\p
	ip.log


My question, I only need to do this to get the psycopg2 working?

    python setup.py build_ext --pg-config /path/to/pg_config build ...

How to install psycopg2 with &quot;pip&quot; on Python?

I&#39;m trying to connect to a Postgres database with SQLAlchemy. I&#39;ve installed psycopg2. However, I get the error `sqlalchemy.exc.NoSuchModuleError: Can&#39;t load plugin: sqlalchemy.dialects:postgres`. How do I configure SQLAlchemy to connect to PostgreSQL?

```python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config[&quot;SQLALCHEMY_DATABASE_URI&quot;] = &quot;postgres://username@localhost:5432/template1&quot;

db = SQLAlchemy(app)
```

sqlalchemy.exc.NoSuchModuleError: Can&#39;t load plugin: sqlalchemy.dialects:postgres

I&#39;m new to Python and Django.

I&#39;m configuring a Django project using a PostgreSQL database engine backend, But I&#39;m getting errors on each database operation. For example when I run `manage.py syncdb`, I&#39;m getting:

	C:\xampp\htdocs\djangodir&gt;python manage.py syncdb
	Traceback (most recent call last):
	  File &quot;manage.py&quot;, line 11, in &lt;module&gt;
		execute_manager(settings)
	  File &quot;C:\Python27\lib\site-packages\django\core\management\__init__.py&quot;, line
	438, in execute_manager
		utility.execute()
	  File &quot;C:\Python27\lib\site-packages\django\core\management\__init__.py&quot;, line
	379, in execute
		self.fetch_command(subcommand).run_from_argv(self.argv)
	  File &quot;C:\Python27\lib\site-packages\django\core\management\__init__.py&quot;, line
	261, in fetch_command
		klass = load_command_class(app_name, subcommand)
	  File &quot;C:\Python27\lib\site-packages\django\core\management\__init__.py&quot;, line
	67, in load_command_class
		module = import_module(&#39;%s.management.commands.%s&#39; % (app_name, name))
	  File &quot;C:\Python27\lib\site-packages\django\utils\importlib.py&quot;, line 35, in im
	port_module
		__import__(name)
	  File &quot;C:\Python27\lib\site-packages\django\core\management\commands\syncdb.py&quot;
	, line 7, in &lt;module&gt;
		from django.core.management.sql import custom_sql_for_model, emit_post_sync_
	signal
	  File &quot;C:\Python27\lib\site-packages\django\core\management\sql.py&quot;, line 6, in
	 &lt;module&gt;
		from django.db import models
	  File &quot;C:\Python27\lib\site-packages\django\db\__init__.py&quot;, line 77, in &lt;modul
	e&gt;
		connection = connections[DEFAULT_DB_ALIAS]
	  File &quot;C:\Python27\lib\site-packages\django\db\utils.py&quot;, line 92, in __getitem
	__
		backend = load_backend(db[&#39;ENGINE&#39;])
	  File &quot;C:\Python27\lib\site-packages\django\db\utils.py&quot;, line 33, in load_back
	end
		return import_module(&#39;.base&#39;, backend_name)
	  File &quot;C:\Python27\lib\site-packages\django\utils\importlib.py&quot;, line 35, in im
	port_module
		__import__(name)
	  File &quot;C:\Python27\lib\site-packages\django\db\backends\postgresql\base.py&quot;, li
	ne 23, in &lt;module&gt;
		raise ImproperlyConfigured(&quot;Error loading psycopg module: %s&quot; % e)
	django.core.exceptions.ImproperlyConfigured: Error loading psycopg module: No mo
	dule named psycopg

Can someone give me a clue on what is going on?

How to set up a PostgreSQL database in Django

I am trying to set up a PostgreSQL database for my django project, which I believe I have done now thanks to the replies to my last question &lt;https://stackoverflow.com/questions/12710715/problems-setting-up-a-postgresql-database-for-a-django-project/12713737#comment17168054_12713737&gt;. I am now trying to run the command &#39;python manage.py runserver&#39; in Terminal to get my localhost up but when I run the command, I see this response...

    Error: No module named psycopg2.extensions

I&#39;m not sure what this means - I have tried to download psycopg2 but can&#39;t seem to find a way to download psycopg2 using homebrew. I have tried easy_install, pip install and sudo but all return errors like this...

    Downloading http://www.psycopg.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.5.tar.gz
    Processing psycopg2-2.4.5.tar.gz
    Writing /tmp/easy_install-l7Qi62/psycopg2-2.4.5/setup.cfg
    Running psycopg2-2.4.5/setup.py -q bdist_egg --dist-dir /tmp/easy_install-l7Qi62/psycopg2-2.4.5/egg-dist-tmp-PBP5Ds
    no previously-included directories found matching &#39;doc/src/_build&#39;
    unable to execute gcc-4.0: No such file or directory
    error: Setup script exited with error: command &#39;gcc-4.0&#39; failed with exit status 1

How to fix this?

Error: No module named psycopg2.extensions

What is the correct method to have the list (countryList) be available via %s in the SQL statement?

    # using psycopg2
    countryList=[&#39;UK&#39;,&#39;France&#39;]

    sql=&#39;SELECT * from countries WHERE country IN (%s)&#39;
    data=[countryList]
    cur.execute(sql,data)

As it is now, it errors out after trying to run &quot;WHERE country in (ARRAY[...])&quot;. Is there a way to do this other than through string manipulation?

Thanks



Python/psycopg2 WHERE IN statement

I was trying to install postgres for a tutorial, but `pip` gives me error:

    pip install psycopg

A snip of error I get:

    Error: pg_config executable not found.
    
    Please add the directory containing pg_config to the PATH
    
    or specify the full executable path with the option:
    
        python setup.py build_ext --pg-config /path/to/pg_config build ...
    
    or with the pg_config option in &#39;setup.cfg&#39;.

Where is `pg_config` in my virtualenv? How to configure it? I&#39;m using virtualenv because I do not want a system-wide installation of postgres.

Mac + virtualenv + pip + postgresql = Error: pg_config executable not found

In my code I use the [`DictCursor`][1] from `psycopg2.extras` like this

    dict_cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
However, all of the sudden I get the following error when I load the cursor:

    AttributeError: &#39;module&#39; object has no attribute &#39;extras&#39;

Maybe something is dorked in my installation but I have no clue where to start looking. I made some updates with pip, but as far as I know no dependencies of `psycopg2`.


  [1]: http://initd.org/psycopg/docs/extras.html#dictionary-like-cursor

psycopg2: AttributeError: &#39;module&#39; object has no attribute &#39;extras&#39;

I am working on a project for one of my lectures and I need to download the package psycopg2 in order to work with the postgresql database in use. Unfortunately, when I try to pip install psycopg2 the following error pops up:

    ld: library not found for -lssl
    clang: error: linker command failed with exit code 1 (use -v to see invocation)
    error: command &#39;/usr/bin/clang&#39; failed with exit status 1
    ld: library not found for -lssl
    clang: error: linker command failed with exit code 1 (use -v to see invocation)
    error: command &#39;/usr/bin/clang&#39; failed with exit status 1
Does anyone know why this is happening? Thanks in advance!

Can&#39;t install psycopg2 package through pip install on MacOS

I&#39;m trying to build a Flask app using Postgres with Docker. I&#39;d like to connect to an AWS RDS instance of Postgres, but use Docker for my Flask app. However, when trying to set up `psycopg2` it runs into an error because it can&#39;t find `pg_config`. Here&#39;s the error:

    Building api
    Step 1/5 : FROM python:3.6.3-alpine3.6
     ---&gt; 84c98ca3b5c5
    Step 2/5 : WORKDIR /usr/src/app
     ---&gt; Using cache
     ---&gt; 407c158f5ee4
    Step 3/5 : COPY . .
     ---&gt; 966df18d329e
    Step 4/5 : RUN pip install -r requirements.txt
     ---&gt; Running in 284cc97aeb63
    Collecting aniso8601==1.3.0 (from -r requirements.txt (line 1))
      Downloading aniso8601-1.3.0.tar.gz (57kB)
    Collecting click==6.7 (from -r requirements.txt (line 2))
      Downloading click-6.7-py2.py3-none-any.whl (71kB)
    Collecting Flask==0.12.2 (from -r requirements.txt (line 3))
      Downloading Flask-0.12.2-py2.py3-none-any.whl (83kB)
    Collecting Flask-RESTful==0.3.6 (from -r requirements.txt (line 4))
      Downloading Flask_RESTful-0.3.6-py2.py3-none-any.whl
    Collecting Flask-SQLAlchemy==2.3.2 (from -r requirements.txt (line 5))
      Downloading Flask_SQLAlchemy-2.3.2-py2.py3-none-any.whl
    Collecting itsdangerous==0.24 (from -r requirements.txt (line 6))
      Downloading itsdangerous-0.24.tar.gz (46kB)
    Collecting Jinja2==2.9.6 (from -r requirements.txt (line 7))
      Downloading Jinja2-2.9.6-py2.py3-none-any.whl (340kB)
    Collecting MarkupSafe==1.0 (from -r requirements.txt (line 8))
      Downloading MarkupSafe-1.0.tar.gz
    Collecting psycopg2==2.7.3.1 (from -r requirements.txt (line 9))
      Downloading psycopg2-2.7.3.1.tar.gz (425kB)
        Complete output from command python setup.py egg_info:
        running egg_info
        creating pip-egg-info/psycopg2.egg-info
        writing pip-egg-info/psycopg2.egg-info/PKG-INFO
        writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
        writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
        writing manifest file &#39;pip-egg-info/psycopg2.egg-info/SOURCES.txt&#39;
        Error: pg_config executable not found.
    
        Please add the directory containing pg_config to the PATH
        or specify the full executable path with the option:
    
            python setup.py build_ext --pg-config /path/to/pg_config build ...
    
        or with the pg_config option in &#39;setup.cfg&#39;.
    
        ----------------------------------------
    Command &quot;python setup.py egg_info&quot; failed with error code 1 in /tmp/pip-build-01lf5grh/psycopg2/
    ERROR: Service &#39;api&#39; failed to build: The command &#39;/bin/sh -c pip install -r requirements.txt&#39; returned a non-zero code: 1

Here&#39;s my `Dockerfile`:

    FROM python:3.6.3-alpine3.6
    
    WORKDIR /usr/src/app
    
    COPY . .
    
    RUN pip install -r requirements.txt
    
    CMD [&quot;python&quot;, &quot;app.py&quot;]

**Many others seem to have a similar issue locally, but none of them involve using Docker. This seems like a Docker issue because I can set up a local virtual environment and the setup works just fine since I have Postgres installed locally and it&#39;s able to find my local `pg_config`.** 

It appears that during the container build/setup, Docker is trying to find `pg_config` within the container. Is there a way to install a `pg_config` in the container, even though I won&#39;t be using a containerized instance of Postgres, but rather the instance on RDS?

Any and all suggestions on how to get around this are welcomed.

Error: pg_config executable not found when installing psycopg2 on Alpine in Docker

What fork, or combination of packages should one to use to make PyPy, Django and PostgreSQL play nice together?

I know that PyPy and Django play nice together, but I am less certain about PyPy and PostgreSQL. I do see that Alex Gaynor has made a fork of PyPy called [pypy-postgresql][1]. I also know that some people are using [psycopg2-ctypes][2].

Is there a difference between these forks? Or should we use the stable 1.9 PyPy and use psycopg2-ctypes? Using the ctypes options could hurt performance, see the comment below.

Also, has anyone experienced any pitfalls with using PyPy with pyscopg2? It seems easy enough to fall back on CPython if something isn&#39;t working right, but mostly I&#39;m looking for things a programmer can do ahead of time to prepare.

I looked around, it doesn&#39;t seem that psycopg2 works natively with PyPy. Although, psycopg2-ctypes does seem to be working for some people,  there was a discussion on [pypy-dev][3]. I work on Windows, and I don&#39;t think psycopg2-ctypes is ready for Windows yet, sadly.


  [1]: https://bitbucket.org/alex_gaynor/pypy-postgresql
  [2]: https://github.com/mvantellingen/psycopg2-ctypes
  [3]: http://mail.python.org/pipermail/pypy-dev/2012-January/009061.html

How do you get PyPy, Django and PostgreSQL to work together?

I tried to install psycopg2 to my environment, but I get the following error:

    (venv)avlahop@apostolos-laptop:~/development/django/rhombus-dental$ sudo pip install psycopg2
    Downloading/unpacking psycopg2,
    Downloading psycopg2-2.5.2.tar.gz (685kB): 685kB downloaded
    Running setup.py egg_info for package psycopg2
    
    Installing collected packages: psycopg2
      Running setup.py install for psycopg2
        building &#39;psycopg2._psycopg&#39; extension
        x86_64-linux-gnu-gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall Wstrict-prototypes -fPIC -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION=&quot;2.5.2 (dt dec pq3 ext)&quot; -DPG_VERSION_HEX=0x09010D -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/usr/include/python2.7 -I. -I/usr/include/postgresql -I/usr/include/postgresql/9.1/server -c psycopg/psycopgmodule.c -o build/temp.linux-x86_64-2.7/psycopg/psycopgmodule.o -Wdeclaration-after-statement
        In file included from psycopg/psycopgmodule.c:27:0:
        ./psycopg/psycopg.h:30:20: fatal error: Python.h: Δεν υπάρχει τέτοιο αρχείο ή κατάλογος
         #include &lt;Python.h&gt;
                        ^
        compilation terminated.
        error: command &#39;x86_64-linux-gnu-gcc&#39; failed with exit status 1
        Complete output from command /usr/bin/python -c &quot;import setuptools;__file__=&#39;/tmp/pip_build_root/psycopg2/setup.py&#39;;exec(compile(open(__file__).read().replace(&#39;\r\n&#39;, &#39;\n&#39;), __file__, &#39;exec&#39;))&quot; install --record /tmp/pip-SgfQCA-record/install-record.txt --single-version-externally-managed:
    running install

    running build

    running build_py

    creating build

    creating build/lib.linux-x86_64-2.7

    creating build/lib.linux-x86_64-2.7/psycopg2

    copying lib/pool.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/errorcodes.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/__init__.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/_json.py -&gt; build/lib.linux-x86_64-2.7/psycopg2
  
    copying lib/_range.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/extensions.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/psycopg1.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/tz.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    copying lib/extras.py -&gt; build/lib.linux-x86_64-2.7/psycopg2

    creating build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/testconfig.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copyng tests/test_bug_gc.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_dates.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_copy.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_cancel.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_bugX000.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_extras_dictcursor.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_psycopg2_dbapi20.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_types_basic.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_async.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_lobject.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_cursor.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_with.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/__init__.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_types_extras.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/testutils.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_notify.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_green.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_quote.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests
  
    copying tests/test_connection.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_transaction.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/dbapi20.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/test_module.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    copying tests/dbapi20_tpc.py -&gt; build/lib.linux-x86_64-2.7/psycopg2/tests

    running build_ext

    building &#39;psycopg2._psycopg&#39; extension

    creating build/temp.linux-x86_64-2.7

    creating build/temp.linux-x86_64-2.7/psycopg

    x86_64-linux-gnu-gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION=&quot;2.5.2 (dt dec pq3 ext)&quot; -DPG_VERSION_HEX=0x09010D -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/usr/include/python2.7 -I. -I/usr/include/postgresql -I/usr/include/postgresql/9.1/server -c psycopg/psycopgmodule.c -o build/temp.linux-x86_64-2.7/psycopg/psycopgmodule.o -Wdeclaration-after-statement

    In file included from psycopg/psycopgmodule.c:27:0:

    ./psycopg/psycopg.h:30:20: fatal error: Python.h: No such file or directory
     #include &lt;Python.h&gt;

                    ^

    compilation terminated.

    error: command &#39;x86_64-linux-gnu-gcc&#39; failed with exit status 1

    ----------------------------------------
    Cleaning up...
    Command /usr/bin/python -c &quot;import setuptools;__file__=&#39;/tmp/pip_build_root/psycopg2/setup.py&#39;;exec(compile(open(__file__).read().replace(&#39;\r\n&#39;, &#39;\n&#39;), __file__, &#39;exec&#39;))&quot; install --record /tmp/pip-SgfQCA-record/install-record.txt --single-version-externally-managed failed with error code 1 in /tmp/pip_build_root/psycopg2
    Traceback (most recent call last):
      File &quot;/usr/bin/pip&quot;, line 9, in &lt;module&gt;
        load_entry_point(&#39;pip==1.4.1&#39;, &#39;console_scripts&#39;, &#39;pip&#39;)()
      File &quot;/usr/lib/python2.7/dist-packages/pip/__init__.py&quot;, line 148, in main
        return command.main(args[1:], options)
      File &quot;/usr/lib/python2.7/dist-packages/pip/basecommand.py&quot;, line 169, in main
        text = &#39;\n&#39;.join(complete_log)
    UnicodeDecodeError: &#39;ascii&#39; codec can&#39;t decode byte 0xce in position 54: ordinal not in range(128)

I also get errors trying to install gunicorn. I followed [this][1] example here but nothing. My virtual environment was created using the `--no-site-packages`flag. I use ubuntu 13.10 and greek environment. Maybe it&#39;s that?


  [1]: https://devcenter.heroku.com/articles/getting-started-with-python

Error trying to install Postgres for python (psycopg2)

Trying to setup postgres with the postgres mac app and hit this error, which I haven&#39;t been able to solve. Any thoughts?

        ImportError: dlopen(/Users/Craig/pyenv/mysite/lib/python2.7/site-packages/psycopg2/_psycopg.so, 2): Library not loaded: @executable_path/../lib/libssl.1.0.0.dylib
      Referenced from: /Applications/Postgres.app/Contents/MacOS/lib/libpq.dylib
      Reason: image not found

Psycopg2 image not found

I&#39;m trying to install `Psycopg2` on my Macbook, but I am getting an error. I found a lot of the same questions on StackOverflow but no answer seems to work. \
I&#39;m using: 

OS: MacOS 10.9.5 \
Python Version: 3.4.3
                     
My error code is:

```
Running setup.py egg_info for package psycopg2  Error: pg_config
executable not found.

Please add the directory containing pg_config to the PATH or specify
the full executable path with the option:

python setup.py build_ext --pg-config /path/to/pg_config build ...
 
or with the pg_config option in &#39;setup.cfg&#39;.  Complete output from
command python setup.py egg_info:  running egg_info

writing pip-egg-info/psycopg2.egg-info/PKG-INFO
 
writing top-level names to
pip-egg-info/psycopg2.egg-info/top_level.txt
 
writing dependency_links to
pip-egg-info/psycopg2.egg-info/dependency_links.txt
 
warning: manifest_maker: standard file &#39;-c&#39; not found
 
Error: pg_config executable not found.

Please add the directory containing pg_config to the PATH or specify the full executable path with the option:
 
python setup.py build_ext --pg-config /path/to/pg_config build ...
 
or with the pg_config option in &#39;setup.cfg&#39;.
 
---------------------------------------- 
Command python setup.py egg_info failed with error code 1 in
/Users/sg/build/psycopg2 Storing complete log in
/Users/sg/Library/Logs/pip.log
```




Error Installing Psycopg2 on MacOS 10.9.5

Can someone please explain how I can get the tables in the current database?

I am using postgresql-8.4 psycopg2.

How do I get tables in postgres using psycopg2?

Is it possible to install [`psycopg2`][psycopg2] into a `virtualenv` when PostgreSQL isn&#39;t installed on my development system—MacBook Pro with OS X 10.6?

When I run `pip install psycopg2` from within my `virtualenv`, I received the error shown below.

I&#39;m trying to connect to a legacy database on a server using Django, and I&#39;d prefer not to install PostgreSQL on my development system if possible.

## Why not install PostgreSQL?

I received an error when installing PostgreSQL using homebrew. I have Xcode4—and only Xcode4—installed on my MacBook Pro and am thinking it&#39;s related to missing gcc 4.0. However, this is a problem for another StackOverflow question.

**Update 8:37 AM on April 12, 2011:** I&#39;d still like to know if this is possible without installing PostgreSQL on my MacBook Pro. However, I ran `brew update` and forced a reinstallation of ossp-uuid with `brew install --force ossp-uuid` and now `brew install postgresql` works. With PostgreSQL successfully installed, I was able to `pip install psycopg2` from within my virtualenv.

## Error from `pip install psycopg2` ##

    $ pip install psycopg2
    Downloading/unpacking psycopg2
      Running setup.py egg_info for package psycopg2
        
        Error: pg_config executable not found.
        
        Please add the directory containing pg_config to the PATH
        or specify the full executable path with the option:
        
            python setup.py build_ext --pg-config /path/to/pg_config build ...
        
        or with the pg_config option in &#39;setup.cfg&#39;.
        Complete output from command python setup.py egg_info:
        running egg_info
    
    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
    warning: manifest_maker: standard file &#39;-c&#39; not found
    
    Error: pg_config executable not found.
    
    Please add the directory containing pg_config to the PATH
    or specify the full executable path with the option:
    
        python setup.py build_ext --pg-config /path/to/pg_config build ...
    
    or with the pg_config option in &#39;setup.cfg&#39;.
    
    ----------------------------------------
    Command python setup.py egg_info failed with error code 1
    Storing complete log in /Users/matthew/.pip/pip.log

## Preliminary Research ##

Below are the articles I read as preliminary research:

* [Installing psycopg2 to use Django with PostgreSQL on OS X][so4430860]
* [Installing psycopg2 on OS X][jonypawks]
* [Using psycopg2 with virtualenv on Ubuntu JauntyLucid][saltycrane]
* [Postgres, psycopg2, virtualenv install hints][koansys]

[koansys]: http://koansys.com/tech/postgres-psycopg2-virtualenv-install-hints

[saltycrane]: http://www.saltycrane.com/blog/2009/07/using-psycopg2-virtualenv-ubuntu-jaunty/
[so4430860]: https://stackoverflow.com/q/4430860/95592
[jonypawks]: http://blog.jonypawks.net/2008/06/20/installing-psycopg2-on-os-x/
[psycopg2]: http://initd.org/psycopg/

Installing psycopg2 into virtualenv when PostgreSQL is not installed on development system

I am following Heroku&#39;s tutorial to deploy a Django app: http://devcenter.heroku.com/articles/django#prerequisites. 

Everything is working fine until I get to this part: 

    $ pip install Django psycopg2

I can install Django by itself, but the probelm is with psycopg2. 

I keep getting this error: 


   

    ld: library not found for -lpq
    
    collect2: ld returned 1 exit status
    
    ld: library not found for -lpq
    
    collect2: ld returned 1 exit status
    
    lipo: can&#39;t open input file: /var/folders/_4/p6l0y0t51hd4xbq7llbyshmw0000gn/T//cc0L10mI.out (No such file or directory)
    
    error: command &#39;gcc-4.2&#39; failed with exit status 1

I&#39;ve installed PostgreSQL 9.1 on my machine. 

Also, in the output, there are bunch of lines like this: 

    gcc-4.2 -fno-strict-aliasing -fno-common -dynamic -isysroot /Developer/SDKs/MacOSX10.6.sdk -arch i386 -arch x86_64 -g -O2 -DNDEBUG -g -O3 -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION=&quot;2.4.4 (dt dec pq3 ext)&quot; -DPG_VERSION_HEX=0x090004 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/usr/include -I/usr/include/postgresql/server -c psycopg/typecast.c -o build/temp.macosx-10.6-intel-2.7/psycopg/typecast.o

I&#39;m not really sure what it means, but I do notice that it has &quot;macosx-10.6&quot; in it so I&#39;m wondering if that could be the issue? I&#39;m on 10.7. 

Thanks in advance for your help. 

Can&#39;t install psycopg2 with pip in virtualenv on Mac OS X 10.7

Are there any examples of how to pass parameters with an SQL query in Pandas?

In particular I&#39;m using an SQLAlchemy engine to connect to a PostgreSQL database.  So far I&#39;ve found that the following works:

    df = psql.read_sql((&#39;select &quot;Timestamp&quot;,&quot;Value&quot; from &quot;MyTable&quot; &#39;
                         &#39;where &quot;Timestamp&quot; BETWEEN %s AND %s&#39;),
                       db,params=[datetime(2014,6,24,16,0),datetime(2014,6,24,17,0)],
                       index_col=[&#39;Timestamp&#39;])
    

The Pandas documentation says that params can also be passed as a dict, but I can&#39;t seem to get this to work having tried for instance:

    df = psql.read_sql((&#39;select &quot;Timestamp&quot;,&quot;Value&quot; from &quot;MyTable&quot; &#39;
                         &#39;where &quot;Timestamp&quot; BETWEEN :dstart AND :dfinish&#39;),
                       db,params={&quot;dstart&quot;:datetime(2014,6,24,16,0),&quot;dfinish&quot;:datetime(2014,6,24,17,0)},
                       index_col=[&#39;Timestamp&#39;])
    

What is the recommended way of running these types of queries from Pandas?

Pandas read_sql with parameters

I have the following code, using pscyopg2:

    sql = &#39;select %s from %s where utctime &gt; %s and utctime &lt; %s order by utctime asc;&#39;
    data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
    rows = cur.mogrify(sql, data)

This outputs:

    select &#39;waterTemp, airTemp, utctime&#39; from &#39;ss2012_t02&#39; where utctime &gt; &#39;2012-05-03T17:01:35+00:00&#39;::timestamptz and utctime &lt; &#39;2012-05-01T17:01:35+00:00&#39;::timestamptz order by utctime asc;


When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.

Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:

    voyage = &#39;ss2012_t02&#39;
    sql = &#39;select %s from &#39; + voyage + &#39; where utctime &gt; %s and utctime &lt; %s order by utctime asc;&#39;

Cheers for any insights.

Passing table name as a parameter in psycopg2

I haven&#39;t worked with psycopg2 before but I&#39;m trying to change the cursor factory to DictCursor so that fetchall or fetchone will return a dictionary instead of a list.

I created a test script to make things simple and only test this functionality. Here&#39;s my little bit of code that I feel should work

    import psycopg2
    import psycopg2.extras
    
    conn = psycopg2.connect(&quot;dbname=%s user=%s password=%s&quot; % (DATABASE, USERNAME, PASSWORD))
    
    cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor)
    cur.execute(&quot;SELECT * from review&quot;)
    
    res = cur.fetchall()
    
    print type(res)
    print res


The res variable is always a list and not a dictionary as I would expect.

A current workaround that I&#39;ve implemented is to use this function that builds a dictionary and run each row returned by fetchall through it.

    def build_dict(cursor, row):
        x = {}
        for key,col in enumerate(cursor.description):
            x[col[0]] = row[key]
        return d


Python is version 2.6.7 and psycopg2 is version 2.4.2.

DictCursor doesn&#39;t seem to work under psycopg2

I&#39;m attempting to make a website with a few others for the first time, and have run into a weird error when trying to use Django/Python/VirtualEnv. I&#39;ve found solutions to this problem for other operating systems, such as Ubuntu, but can&#39;t find any good solutions for Mac. 

This is the relevant code being run:

    virtualenv -p python3 venv
    source venv/bin/activate
    pip install -r requirements.txt

After running that block, I get the following errors:

&gt;  AssertionError
  
&gt;  ----------------------------------------
&gt;  Failed building wheel for django-toolbelt
&gt;  Running setup.py bdist_wheel for psycopg2

  ...

&gt;  AssertionError
  
&gt;  ----------------------------------------
&gt;  Failed building wheel for psycopg2
&gt;  Failed to build django-toolbelt psycopg2
  
I believe I&#39;ve installed the &quot;django-toolbelt&quot; and &quot;psycopg2&quot;, so I&#39;m not sure why it would be failing. 

The only difference I can think of is that I did not use the command

    sudo apt-get install libpq-dev
as was instructed for Ubuntu usage as I believe that installing postgresql with brew took care of the header. 

Thanks for any help or insight! 


&quot;Failed building wheel for psycopg2&quot; - MacOSX using virtualenv and pip

I want to create Postgres database using Python.

    con = psql.connect(dbname=&#39;postgres&#39;,
          user=self.user_name, host=&#39;&#39;,
          password=self.password)

    cur = con.cursor()
    cur.execute(&quot;CREATE DATABASE %s  ;&quot; % self.db_name)

I am getting the following error:

    InternalError: CREATE DATABASE cannot run inside a transaction block
I am using psycopg2 to connect. I don&#39;t understand what&#39;s the problem.
What am I trying to do is to connect to database (Postgres):

    psql -postgres -U UserName
And then create another database:

    create database test;
This is what I usually do and I want to automate this by creating Python script.


Create a Postgres database using python

Presently, I&#39;m attempting to configure Django for use on a project, and am encountering a persistent error when I try to run `python manage.py syncdb`.

    File &quot;/x/x/x/x/x/x/base.py&quot;, line 23, in ?
        raise ImproperlyConfigured(&quot;Error loading psycopg module: %s&quot; % e)
    django.core.exceptions.ImproperlyConfigured: Error loading psycopg module: No module named psycopg

The other discussions of a similar problem that I have found both here and on other sites ended up being solved by the user downloading and installing psycopg, but unless I&#39;m missing something critical, I&#39;ve done that several times. I downloaded the tarball, unpacked it on my server, ran `python setup.py build` and `python setup.py install` with a --prefix directing it to my existing Python Path...I can go and navigate to the folder and see the various psycopg files, but for whatever reason, when I try to get my new project to sync up with the database, it cannot find psycopg and, as a result, won&#39;t talk to the db.

**Notes: hostmonster.com account, have tried multiple release versions of psycopg2.**

django.core.exceptions.ImproperlyConfigured: Error loading psycopg module: No module named psycopg

I need to insert JSON data from tornado to postgres, so here&#39;s test like this:

    from psycopg2 import connect
     
    conn = connect(&quot;user=&#39;pguser&#39; host=&#39;localhost&#39; dbname=&#39;pgdb&#39; password=&#39;pgpass&#39;&quot;)
    cursor = conn.cursor()
    
    data = &#39;[{&quot;id&quot;:&quot;sdf&quot;,&quot;name&quot;:&quot;wqe&quot;,&quot;author&quot;:&quot;vb&quot;}]&#39;
    
    for row in eval(data):
      print row
      cursor.execute(&quot;&quot;&quot;INSERT INTO books(id,name,author) VALUES(&#39;%s&#39;,&#39;%s&#39;,&#39;%s&#39;)&quot;&quot;&quot; % \
    		(row[&#39;id&#39;], row[&#39;name&#39;], row[&#39;author&#39;])
      )
    
    &gt;&gt;&gt; cursor.execute(&quot;SELECT * FROM books&quot;)
    &gt;&gt;&gt; cursor.fetchall()
    [(&#39;sdf&#39;, &#39;wqe&#39;, &#39;vb&#39;)]
    &gt;&gt;&gt; 
    $&gt; psql -d pgdb -U pguser -W
    Password for user pguser: 
    psql (9.1.6)
    Type &quot;help&quot; for help.
    
    pgdb=&gt; select * from books;
     id | name | author 
    ----+------+--------
    (0 rows)


As you can see after doing `select` in python shell, there&#39;s some data, but in psql there&#39;s
0 rows! What may I be doing wrong?

Python 2.7.2+


psycopg2 not actually inserting data

What&#39;s the best way to make psycopg2 pass parameterized queries to PostgreSQL? I don&#39;t want to write my own escpaing mechanisms or adapters and the psycopg2 source code and examples are difficult to read in a web browser.

If I need to switch to something like PyGreSQL or another python pg adapter, that&#39;s fine with me. I just want simple parameterization.

Parameterized queries with psycopg2 / Python DB-API and PostgreSQL

Currently, I have the following method to execute INSERT/UPDATE/DELETE statements using `psycopg2` in `Python`:

    def exec_statement(_cxn, _stmt):
        try:
    	    db_crsr = _cxn.cursor()
    	    db_crsr.execute(_stmt)
    	    _cxn.commit()
    	    db_crsr.close()
    	    return True
        except:
    	    return False

But what I would really like it to do, instead of bool, is return the row count affected by the transaction or -1 if the operation fails.

Is there a way to get a number of rows affected by `_cxn.commit()`?  E.g. for a single INSERT it would be always 1, for a DELETE or UPDATE, the number of rows affected by the statement etc.?

Getting affected row count from psycopg2 connection.commit()

Let&#39;s say I have a json that looks like this:

    some_json = {&#39;key_a&#39;: {&#39;nested_key&#39;: &#39;a&#39;},
                 &#39;key_b&#39;: {&#39;nested_key&#39;: &#39;b&#39;}}

Note that `key_a` and `key_b` are optional keys mapped to dictionaries and may or may not exist.

I have a function that checks if an outer key exists in `some_json` and returns a boolean.

    CREATE FUNCTION key_exists(some_json json, outer_key text)
    RETURNS boolean AS $$
    BEGIN
        RETURN (some_json-&gt;outer_key IS NULL);
    END;
    $$ LANGUAGE plpgsql;

I get the following error:

    ProgrammingError: operator does not exist: json -&gt; boolean

Why is `outer_key` equating to a boolean? What&#39;s the proper syntax to perform this check?

How do I check if a json key exists in Postgres?

How can I determine if a table exists using the Psycopg2 Python library?  I want a true or false boolean.

All Psycopg2 Solutions on Gang of Coders

Total of 46 Psycopg2 Solutions