标签归档：psycopg2

知识问答

如何从psycopg2游标获取列名列表？

问题：如何从psycopg2游标获取列名列表？

我想要一种直接从所选列名直接生成列标签的通用方法，并且回想起看到python的psycopg2模块支持此功能的情况。

点击查看英文原文

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

回答 0

摘自Mark Lutz的“ Programming Python”：

curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]
点击查看英文原文

From “Programming Python” by Mark Lutz:

curs.execute("Select * FROM people LIMIT 0")
colnames = [desc[0] for desc in curs.description]

回答 1

您可以做的另一件事是创建一个游标，您可以使用该游标通过列名来引用您的列（这是导致我首先进入此页面的需要）：

import psycopg2
from psycopg2.extras import RealDictCursor

ps_conn = psycopg2.connect(...)
ps_cursor = psql_conn.cursor(cursor_factory=RealDictCursor)

ps_cursor.execute('select 1 as col_a, 2 as col_b')
my_record = ps_cursor.fetchone()
print (my_record['col_a'],my_record['col_b'])

>> 1, 2
点击查看英文原文

Another thing you can do is to create a cursor with which you will be able to reference your columns by their names (that’s a need which led me to this page in the first place):

import psycopg2
from psycopg2.extras import RealDictCursor

ps_conn = psycopg2.connect(...)
ps_cursor = psql_conn.cursor(cursor_factory=RealDictCursor)

ps_cursor.execute('select 1 as col_a, 2 as col_b')
my_record = ps_cursor.fetchone()
print (my_record['col_a'],my_record['col_b'])

>> 1, 2

回答 2

要在单独的查询中获取列名，可以查询information_schema.columns表。

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []

  with psycopg2.connect(DSN) as connection:
      with connection.cursor() as cursor:
          cursor.execute("select column_name from information_schema.columns where table_schema = 'YOUR_SCHEMA_NAME' and table_name='YOUR_TABLE_NAME'")
          column_names = [row[0] for row in cursor]

  print("Column names: {}\n".format(column_names))

要在与数据行相同的查询中获取列名，可以使用游标的描述字段： 

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []
  data_rows = []

  with psycopg2.connect(DSN) as connection:
    with connection.cursor() as cursor:
      cursor.execute("select field1, field2, fieldn from table1")
      column_names = [desc[0] for desc in cursor.description]
      for row in cursor:
        data_rows.append(row)

  print("Column names: {}\n".format(column_names))
点击查看英文原文

To get the column names in a separate query, you can query the information_schema.columns table.

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []

  with psycopg2.connect(DSN) as connection:
      with connection.cursor() as cursor:
          cursor.execute("select column_name from information_schema.columns where table_schema = 'YOUR_SCHEMA_NAME' and table_name='YOUR_TABLE_NAME'")
          column_names = [row[0] for row in cursor]

  print("Column names: {}\n".format(column_names))

To get column names in the same query as data rows, you can use the description field of the cursor: 

#!/usr/bin/env python3

import psycopg2

if __name__ == '__main__':
  DSN = 'host=YOUR_DATABASE_HOST port=YOUR_DATABASE_PORT dbname=YOUR_DATABASE_NAME user=YOUR_DATABASE_USER'

  column_names = []
  data_rows = []

  with psycopg2.connect(DSN) as connection:
    with connection.cursor() as cursor:
      cursor.execute("select field1, field2, fieldn from table1")
      column_names = [desc[0] for desc in cursor.description]
      for row in cursor:
        data_rows.append(row)

  print("Column names: {}\n".format(column_names))

回答 3

如果要从数据库查询中获取命名元组obj，可以使用以下代码段：

from collections import namedtuple

def create_record(obj, fields):
    ''' given obj from db returns named tuple with fields mapped to values '''
    Record = namedtuple("Record", fields)
    mappings = dict(zip(fields, obj))
    return Record(**mappings)

cur.execute("Select * FROM people")
colnames = [desc[0] for desc in cur.description]
rows = cur.fetchall()
cur.close()
result = []
for row in rows:
    result.append(create_record(row, colnames))

这使您可以将记录值当作类属性来访问，即

record.id，record.other_table_column_name等。

甚至更短

from psycopg2.extras import NamedTupleCursor
with cursor(cursor_factory=NamedTupleCursor) as cur:
   cur.execute("Select * ...")
   return cur.fetchall()
点击查看英文原文

If you want to have a named tuple obj from db query you can use the following snippet:

from collections import namedtuple

def create_record(obj, fields):
    ''' given obj from db returns named tuple with fields mapped to values '''
    Record = namedtuple("Record", fields)
    mappings = dict(zip(fields, obj))
    return Record(**mappings)

cur.execute("Select * FROM people")
colnames = [desc[0] for desc in cur.description]
rows = cur.fetchall()
cur.close()
result = []
for row in rows:
    result.append(create_record(row, colnames))

This allows you to access record values as if they were class properties i.e.

record.id, record.other_table_column_name, etc.

or even shorter

from psycopg2.extras import NamedTupleCursor
with cursor(cursor_factory=NamedTupleCursor) as cur:
   cur.execute("Select * ...")
   return cur.fetchall()

回答 4

执行SQL查询后，编写以下2.7中编写的python脚本

total_fields = len(cursor.description)    
fields_names = [i[0] for i in cursor.description   
    Print fields_names
点击查看英文原文

After executing SQL query write following python script written in 2.7

total_fields = len(cursor.description)    
fields_names = [i[0] for i in cursor.description   
    Print fields_names

回答 5

我注意到，您必须cursor.fetchone()在查询后使用来获取中cursor.description（即[desc[0] for desc in curs.description]）中的列列表

点击查看英文原文

I have noticed that you must use cursor.fetchone() after the query to get the list of columns in cursor.description (i.e in [desc[0] for desc in curs.description])

回答 6

我也曾经遇到过类似的问题。我用一个简单的技巧来解决这个问题。假设列表中有列名，例如

col_name = ['a', 'b', 'c']

然后您可以执行以下操作

for row in cursor.fetchone():
    print zip(col_name, row)
点击查看英文原文

I also used to face similar issue. I use a simple trick to solve this. Suppose you have column names in a list like

col_name = ['a', 'b', 'c']

Then you can do following

for row in cursor.fetchone():
    print zip(col_name, row)

回答 7

 # You can use this function
 def getColumns(cursorDescription):
     columnList = []
     for tupla in cursorDescription:
         columnList.append(tupla[0])
     return columnList
点击查看英文原文 
 # You can use this function
 def getColumns(cursorDescription):
     columnList = []
     for tupla in cursorDescription:
         columnList.append(tupla[0])
     return columnList

回答 8

#!/usr/bin/python
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys

def main():
    conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
    # print the connection string we will use to connect
    print "Connecting to database\n ->%s" % (conn_string)

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this query to perform queries
    # note that in this example we pass a cursor_factory argument that will
    # dictionary cursor so COLUMNS will be returned as a dictionary so we
    # can access columns by their name instead of index.
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    # tell postgres to use more work memory
    work_mem = 2048

    # by passing a tuple as the 2nd argument to the execution function our
    # %s string variable will get replaced with the order of variables in
    # the list. In this case there is only 1 variable.
    # Note that in python you specify a tuple with one item in it by placing
    # a comma after the first variable and surrounding it in parentheses.
    cursor.execute('SET work_mem TO %s', (work_mem,))

    # Then we get the work memory we just set -> we know we only want the
    # first ROW so we call fetchone.
    # then we use bracket access to get the FIRST value.
    # Note that even though we've returned the columns by name we can still
    # access columns by numeric index as well - which is really nice.
    cursor.execute('SHOW work_mem')

    # Call fetchone - which will fetch the first row returned from the
    # database.
    memory = cursor.fetchone()

    # access the column by numeric index:
    # even though we enabled columns by name I'm showing you this to
    # show that you can still access columns by index and iterate over them.
    print "Value: ", memory[0]

    # print the entire row 
    print "Row: ", memory

if __name__ == "__main__":
    main()
点击查看英文原文 
#!/usr/bin/python
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys

def main():
    conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
    # print the connection string we will use to connect
    print "Connecting to database\n ->%s" % (conn_string)

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this query to perform queries
    # note that in this example we pass a cursor_factory argument that will
    # dictionary cursor so COLUMNS will be returned as a dictionary so we
    # can access columns by their name instead of index.
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    # tell postgres to use more work memory
    work_mem = 2048

    # by passing a tuple as the 2nd argument to the execution function our
    # %s string variable will get replaced with the order of variables in
    # the list. In this case there is only 1 variable.
    # Note that in python you specify a tuple with one item in it by placing
    # a comma after the first variable and surrounding it in parentheses.
    cursor.execute('SET work_mem TO %s', (work_mem,))

    # Then we get the work memory we just set -> we know we only want the
    # first ROW so we call fetchone.
    # then we use bracket access to get the FIRST value.
    # Note that even though we've returned the columns by name we can still
    # access columns by numeric index as well - which is really nice.
    cursor.execute('SHOW work_mem')

    # Call fetchone - which will fetch the first row returned from the
    # database.
    memory = cursor.fetchone()

    # access the column by numeric index:
    # even though we enabled columns by name I'm showing you this to
    # show that you can still access columns by index and iterate over them.
    print "Value: ", memory[0]

    # print the entire row 
    print "Row: ", memory

if __name__ == "__main__":
    main()
知识问答

SQLAlchemy：引擎，连接和会话的区别

问题：SQLAlchemy：引擎，连接和会话的区别

我使用SQLAlchemy并至少有三个实体：engine，session并且connection，其中有execute方法，所以如果我如想选择所有记录table我能做到这一点

engine.execute(select([table])).fetchall()

还有这个

connection.execute(select([table])).fetchall()

甚至这个

session.execute(select([table])).fetchall()

-结果将是相同的。

据我了解，如果有人使用engine.execute它connection，它会创建，打开session（Alchemy会为您处理）并执行查询。但是，执行此任务的这三种方式之间是否存在全局差异？

点击查看英文原文

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?

回答 0

单行概述：

的行为execute()是在所有情况下相同，但它们是3种不同的方法，在Engine，Connection和Session类。

到底是什么execute()：

要了解行为，execute()我们需要调查Executable该类。Executable是所有“语句”类型对象的超类，包括select（），delete（），update（），insert（），text（）-用最简单的词来说，Executable是SQLAlchemy支持的SQL表达式构造。

在所有情况下，该execute()方法均采用SQL文本或构造的SQL表达式，即SQLAlchemy支持的各种SQL表达式构造，并返回查询结果（ResultProxya-包装DB-API游标对象以更轻松地访问行列。）

为了进一步澄清（仅用于概念澄清，不建议使用方法）：

除了Engine.execute()（无连接执行），Connection.execute()和之外Session.execute()，还可以execute()直接在任何Executable构造上使用。该Executable班有它自己的执行execute()-每个正式文件作为，对什么人一行说明execute()确实是“ 编译并执行这个Executable ”。在这种情况下，我们需要将Executable（SQL表达式构造）与Connection对象或Engine对象（隐式获取Connection对象）进行显式绑定，以便execute()将知道在何处执行SQL。

下面的示例很好地演示了它-给定如下表：

from sqlalchemy import MetaData, Table, Column, Integer

meta = MetaData()
users_table = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)))

显式执行，即Connection.execute()-将SQL文本或构造的SQL表达式传递给以下execute()方法Connection：

engine = create_engine('sqlite:///file.db')
connection = engine.connect()
result = connection.execute(users_table.select())
for row in result:
    # ....
connection.close()

显式无连接执行，即Engine.execute()-将SQL文本或构造的SQL表达式直接传递给execute()Engine方法：

engine = create_engine('sqlite:///file.db')
result = engine.execute(users_table.select())
for row in result:
    # ....
result.close()

隐式执行（即Executable.execute()-）也是无连接的，并且调用的execute()方法Executable，即它execute()直接在SQL表达式构造（的实例Executable）本身上调用方法。

engine = create_engine('sqlite:///file.db')
meta.bind = engine
result = users_table.select().execute()
for row in result:
    # ....
result.close()

注意：出于说明的目的，陈述了隐式执行示例-强烈建议不按照这种方式执行这种执行方式-按照docs：

“隐式执行”是一个非常古老的使用模式，在大多数情况下，它比有用的方法更令人困惑，并且不鼓励使用它。两种模式似乎都鼓励在应用程序设计中过度使用权宜之计的“捷径”，这会在以后导致问题。

你的问题：

据我了解，如果有人使用engine.execute，它将创建连接，打开会话（Alchemy会为您关心）并执行查询。

您认为“如果有人使用engine.execute它会创建connection” 这一部分是正确的，但对于“打开session（炼金术会为您关心）并执行查询”而言，您是正确的- 在形式上，使用Engine.execute()和Connection.execute()（几乎）是同一件事，在形式上，Connection对象是隐式创建的，在以后的情况下，我们显式实例化它。在这种情况下真正发生的是：

`Engine` object (instantiated via `create_engine()`) -> `Connection` object (instantiated via `engine_instance.connect()`) -> `connection.execute({*SQL expression*})`

但是，执行此任务的这三种方式之间是否存在全局差异？

在数据库层，这完全是同一回事，所有这些都在执行SQL（文本表达式或各种SQL表达式构造）。从应用程序的角度来看，有两个选项：

  • 直接执行-使用Engine.execute()或Connection.execute()
  • 使用sessions-通过有效地处理交易单单元的工作，轻松session.add()，session.rollback()，session.commit()，session.close()。在ORM（即映射表）的情况下，这是与DB进行交互的方式。提供identity_map，以便在单个请求期间立即获取已被访问的对象或新创建/添加的对象。

Session.execute()最终使用Connection.execute()语句执行方法来执行SQL语句。使用Session对象是SQLAlchemy ORM建议的应用程序与数据库交互的方式。

文档摘录：

重要的是要注意，在使用SQLAlchemy ORM时，通常不访问这些对象。而是将Session对象用作数据库的接口。但是，对于围绕直接使用文本SQL语句和/或SQL表达式构造而无需ORM更高级别的管理服务参与的应用程序，“引擎”和“连接”为王（也是王后？），请继续阅读。

点击查看英文原文

A one-line overview:

The behavior of execute() is same in all the cases, but they are 3 different methods, in Engine, Connection, and Session classes.

What exactly is execute():

To understand behavior of execute() we need to look into the Executable class. Executable is a superclass for all “statement” types of objects, including select(), delete(),update(), insert(), text() – in simplest words possible, an Executable is a SQL expression construct supported in SQLAlchemy.

In all the cases the execute() method takes the SQL text or constructed SQL expression i.e. any of the variety of SQL expression constructs supported in SQLAlchemy and returns query results (a ResultProxy – Wraps a DB-API cursor object to provide easier access to row columns.)

To clarify it further (only for conceptual clarification, not a recommended approach):

In addition to Engine.execute() (connectionless execution), Connection.execute(), and Session.execute(), it is also possible to use the execute() directly on any Executable construct. The Executable class has it’s own implementation of execute() – As per official documentation, one line description about what the execute() does is “Compile and execute this Executable“. In this case we need to explicitly bind the Executable (SQL expression construct) with a Connection object or, Engine object (which implicitly get a Connection object), so the execute() will know where to execute the SQL.

The following example demonstrates it well – Given a table as below:

from sqlalchemy import MetaData, Table, Column, Integer

meta = MetaData()
users_table = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)))

Explicit execution i.e. Connection.execute() – passing the SQL text or constructed SQL expression to the execute() method of Connection:

engine = create_engine('sqlite:///file.db')
connection = engine.connect()
result = connection.execute(users_table.select())
for row in result:
    # ....
connection.close()

Explicit connectionless execution i.e. Engine.execute() – passing the SQL text or constructed SQL expression directly to the execute() method of Engine:

engine = create_engine('sqlite:///file.db')
result = engine.execute(users_table.select())
for row in result:
    # ....
result.close()

Implicit execution i.e. Executable.execute() – is also connectionless, and calls the execute() method of the Executable, that is, it calls execute() method directly on the SQL expression construct (an instance of Executable) itself.

engine = create_engine('sqlite:///file.db')
meta.bind = engine
result = users_table.select().execute()
for row in result:
    # ....
result.close()

Note: Stated the implicit execution example for the purpose of clarification – this way of execution is highly not recommended – as per docs:

“implicit execution” is a very old usage pattern that in most cases is more confusing than it is helpful, and its usage is discouraged. Both patterns seem to encourage the overuse of expedient “short cuts” in application design which lead to problems later on.

Your questions:

As I understand if someone use engine.execute it creates connection, opens session (Alchemy cares about it for you) and executes query.

You’re right for the part “if someone use engine.execute it creates connection ” but not for “opens session (Alchemy cares about it for you) and executes query ” – Using Engine.execute() and Connection.execute() is (almost) one the same thing, in formal, Connection object gets created implicitly, and in later case we explicitly instantiate it. What really happens in this case is:

`Engine` object (instantiated via `create_engine()`) -> `Connection` object (instantiated via `engine_instance.connect()`) -> `connection.execute({*SQL expression*})`

But is there a global difference between these three ways of performing such task?

At DB layer it’s exactly the same thing, all of them are executing SQL (text expression or various SQL expression constructs). From application’s point of view there are two options:

  • Direct execution – Using Engine.execute() or Connection.execute()
  • Using sessions – efficiently handles transaction as single unit-of-work, with ease via session.add(), session.rollback(), session.commit(), session.close(). It is the way to interact with the DB in case of ORM i.e. mapped tables. Provides identity_map for instantly getting already accessed or newly created/added objects during a single request.

Session.execute() ultimately uses Connection.execute() statement execution method in order to execute the SQL statement. Using Session object is SQLAlchemy ORM’s recommended way for an application to interact with the database.

An excerpt from the docs:

Its important to note that when using the SQLAlchemy ORM, these objects are not generally accessed; instead, the Session object is used as the interface to the database. However, for applications that are built around direct usage of textual SQL statements and/or SQL expression constructs without involvement by the ORM’s higher level management services, the Engine and Connection are king (and queen?) – read on.

回答 1

Nabeel的答案涵盖了很多细节并且很有帮助，但是我发现难以理解。由于这是该问题的第一个Google结果，因此，我对以后发现此问题的人们加深了理解：

运行.execute（）

正如OP和Nabell Ahmed都指出的那样，执行平原时SELECT * FROM tablename，提供的结果没有区别。

这三个对象之间的区别取决于上下文就成为非常重要的SELECT声明中，或者更常见的是，当你想要做其他事情一样使用INSERT，DELETE等等。

何时使用引擎，连接，会话

  • 引擎是SQLAlchemy使用的最低级别的对象。它维护了一个连接池，可在应用程序需要与数据库对话时使用。.execute()是一种先调用conn = engine.connect(close_with_result=True)然后调用的便捷方法conn.execute()。close_with_result参数表示连接自动关闭。（我稍微解释了源代码，但本质上是正确的）。编辑：这是engine.execute的源代码

    您可以使用引擎执行原始SQL。

    result = engine.execute('SELECT * FROM tablename;')
#what engine.execute() is doing under the hood
conn = engine.connect(close_with_result=True)
result = conn.execute('SELECT * FROM tablename;')

#after you iterate over the results, the result and connection get closed
for row in result:
    print(result['columnname']

#or you can explicitly close the result, which also closes the connection
result.close()

    在基本用法下的文档中对此进行了介绍。

  • 连接（正如我们在上面看到的）实际上是执行SQL查询的工作。每当您想更好地控制连接的属性，何时关闭连接等时，都应该执行此操作。例如，非常重要的示例是Transaction，它使您可以决定何时将更改提交到数据库。在正常使用中，更改是自动提交的。通过使用事务，您可以（例如）运行多个不同的SQL语句，如果其中一个出现问题，则可以立即撤消所有更改。

    connection = engine.connect()
trans = connection.begin()
try:
    connection.execute("INSERT INTO films VALUES ('Comedy', '82 minutes');")
    connection.execute("INSERT INTO datalog VALUES ('added a comedy');")
    trans.commit()
except:
    trans.rollback()
    raise

    如果一次失败，这将使您撤消两项更改，就像您忘记创建数据日志表一样。

    因此，如果您正在执行原始SQL代码并需要控制，请使用连接

  • 会话用于SQLAlchemy的对象关系管理（ORM）方面（实际上，您可以从它们的导入方式中看到这一点：）from sqlalchemy.orm import sessionmaker。他们在后台使用连接和事务来运行其自动生成的SQL语句。.execute()是一个便捷功能，可传递到会话绑定的任何对象（通常是引擎，但可以是连接）。

    如果您使用的是ORM功能，请使用会话。如果只执行不绑定对象的直接SQL查询，则最好直接使用连接。

点击查看英文原文

Nabeel’s answer covers a lot of details and is helpful, but I found it confusing to follow. Since this is currently the first Google result for this issue, adding my understanding of it for future people that find this question:

Running .execute()

As OP and Nabell Ahmed both note, when executing a plain SELECT * FROM tablename, there’s no difference in the result provided.

The differences between these three objects do become important depending on the context that the SELECT statement is used in or, more commonly, when you want to do other things like INSERT, DELETE, etc.

When to use Engine, Connection, Session generally

  • Engine is the lowest level object used by SQLAlchemy. It maintains a pool of connections available for use whenever the application needs to talk to the database. .execute() is a convenience method that first calls conn = engine.connect(close_with_result=True) and the then conn.execute(). The close_with_result parameter means the connection is closed automatically. (I’m slightly paraphrasing the source code, but essentially true). edit: Here’s the source code for engine.execute

    You can use engine to execute raw SQL.

    result = engine.execute('SELECT * FROM tablename;')
#what engine.execute() is doing under the hood
conn = engine.connect(close_with_result=True)
result = conn.execute('SELECT * FROM tablename;')

#after you iterate over the results, the result and connection get closed
for row in result:
    print(result['columnname']

#or you can explicitly close the result, which also closes the connection
result.close()

    This is covered in the docs under basic usage.

  • Connection is (as we saw above) the thing that actually does the work of executing a SQL query. You should do this whenever you want greater control over attributes of the connection, when it gets closed, etc. For example, a very import example of this is a Transaction, which lets you decide when to commit your changes to the database. In normal use, changes are autocommitted. With the use of transactions, you could (for example) run several different SQL statements and if something goes wrong with one of them you could undo all the changes at once.

    connection = engine.connect()
trans = connection.begin()
try:
    connection.execute("INSERT INTO films VALUES ('Comedy', '82 minutes');")
    connection.execute("INSERT INTO datalog VALUES ('added a comedy');")
    trans.commit()
except:
    trans.rollback()
    raise

    This would let you undo both changes if one failed, like if you forgot to create the datalog table.

    So if you’re executing raw SQL code and need control, use connections

  • Sessions are used for the Object Relationship Management (ORM) aspect of SQLAlchemy (in fact you can see this from how they’re imported: from sqlalchemy.orm import sessionmaker). They use connections and transactions under the hood to run their automatically-generated SQL statements. .execute() is a convenience function that passes through to whatever the session is bound to (usually an engine, but can be a connection).

    If you’re using the ORM functionality, use session; if you’re only doing straight SQL queries not bound to objects, you’re probably better off using connections directly.

回答 2

这是运行诸如GRANT之类的DCL（数据控制语言）的示例

def grantAccess(db, tb, user):
  import sqlalchemy as SA
  import psycopg2

  url = "{d}+{driver}://{u}:{p}@{h}:{port}/{db}".\
            format(d="redshift",
            driver='psycopg2',
            u=username,
            p=password,
            h=host,
            port=port,
            db=db)
  engine = SA.create_engine(url)
  cnn = engine.connect()
  trans = cnn.begin()
  strSQL = "GRANT SELECT on table " + tb + " to " + user + " ;"
  try:
      cnn.execute(strSQL)
      trans.commit()
  except:
      trans.rollback()
      raise
点击查看英文原文

Here is an example of running DCL (Data Control Language) such as GRANT

def grantAccess(db, tb, user):
  import sqlalchemy as SA
  import psycopg2

  url = "{d}+{driver}://{u}:{p}@{h}:{port}/{db}".\
            format(d="redshift",
            driver='psycopg2',
            u=username,
            p=password,
            h=host,
            port=port,
            db=db)
  engine = SA.create_engine(url)
  cnn = engine.connect()
  trans = cnn.begin()
  strSQL = "GRANT SELECT on table " + tb + " to " + user + " ;"
  try:
      cnn.execute(strSQL)
      trans.commit()
  except:
      trans.rollback()
      raise
知识问答

安装psycopg2时出错，找不到用于-lssl的库

问题：安装psycopg2时出错，找不到用于-lssl的库

我跑 

sudo pip install psycopg2

我得到了一堆看起来像这样的输出： 

cc -DNDEBUG -g -fwrapv -Os .....
.....
cc -DNDEBUG -g -fwrapv -Os .....
.....

最后，它说：

ld: library not found for -lssl

clang: error: linker command failed with exit code 1 (use -v to see invocation)

error: command 'cc' failed with exit status 1

----------------------------------------
Cleaning up...
Command /usr/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/bz/pvj1g9xj16d10pjjgbrfl3fw0000gn/T/pip_build_root/psycopg2/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" 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

最后，运行easy_install或从源代码进行操作都给我相同的错误（关于-lssl找不到关于库的部分）。

运行brew安装（或升级）openssl将产生以下结果

$ brew upgrade openssl
Error: openssl-1.0.1h already installed

谁能帮我吗？

点击查看英文原文

I run 

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 'cc' failed with exit status 1

----------------------------------------
Cleaning up...
Command /usr/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/bz/pvj1g9xj16d10pjjgbrfl3fw0000gn/T/pip_build_root/psycopg2/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" 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?

回答 0

对于在macOS Sierra 10.12（或更高版本，最有可能）上寻求解决方案的用户：我通过安装命令行工具来解决此问题：

xcode-select --install

在那之后，pip install psycopg2应该工作。

如果没有，您也可以尝试链接到brew的openssl：

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2

通过brew安装openssl。请注意，brew link openssl --force不再起作用：

$ brew link openssl --force                                                                                 17.5s
Warning: Refusing to link: openssl
Linking keg-only openssl means you may end up linking against the insecure,
deprecated system OpenSSL while using the headers from Homebrew's openssl.
Instead, pass the full include/library paths to your compiler e.g.:
  -I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib

正如@macho在下面指出的，如果这仍然不起作用，则可能需要使用--no-cachepip选项，例如

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip --no-cache install psycopg2
点击查看英文原文

For anyone looking for a solution for this on macOS Sierra 10.12 (or later, most likely): I fixed this by installing the command line tools:

xcode-select --install

After that, pip install psycopg2 should work.

If it doesn’t, you could also try to link against brew’s openssl:

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2

with openssl installed via brew. Note that the brew link openssl --force does not work anymore:

$ brew link openssl --force                                                                                 17.5s
Warning: Refusing to link: openssl
Linking keg-only openssl means you may end up linking against the insecure,
deprecated system OpenSSL while using the headers from Homebrew's openssl.
Instead, pass the full include/library paths to your compiler e.g.:
  -I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib

As @macho points out below if this still does not work, you might need to use the --no-cache option of pip, e.g.

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip --no-cache install psycopg2

回答 1

我从brew（brew install openssl）安装了OpenSSL

以下为我工作：

export LDFLAGS="-L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/openssl/include"
pip install psycopg2
点击查看英文原文

I had OpenSSL installed from brew (brew install openssl)

The following worked for me:

export LDFLAGS="-L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/openssl/include"
pip install psycopg2

回答 2

运行时，brew link openssl我收到以下消息：

$ brew link openssl
Warning: Refusing to link: openssl
Linking keg-only openssl means you may end up linking against the insecure,
deprecated system OpenSSL while using the headers from Homebrew's openssl.
Instead, pass the full include/library paths to your compiler e.g.:
  -I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib

遵循此建议，这是pip您需要使用的命令：

$ pip install -r requirements.txt --global-option=build_ext --global-option="-I/usr/local/opt/openssl/include" --global-option="-L/usr/local/opt/openssl/lib"
点击查看英文原文

When running brew link openssl I get the following message:

$ brew link openssl
Warning: Refusing to link: openssl
Linking keg-only openssl means you may end up linking against the insecure,
deprecated system OpenSSL while using the headers from Homebrew's openssl.
Instead, pass the full include/library paths to your compiler e.g.:
  -I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib

Following this advice here’s the pip command you need to use:

$ pip install -r requirements.txt --global-option=build_ext --global-option="-I/usr/local/opt/openssl/include" --global-option="-L/usr/local/opt/openssl/lib"

回答 3

对我有用的是命令中提供的链接openssl的提示，

$ brew link openssl
Warning: Refusing to link macOS-provided software: openssl
If you need to have openssl first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl/include"

$ export LDFLAGS="-L/usr/local/opt/openssl/lib"
$ export CPPFLAGS="-I/usr/local/opt/openssl/include"

$ pip install psycopg2
Collecting psycopg2
  Using cached https://files.pythonhosted.org/packages/23/7e/93c325482c328619870b6cd09370f6dbe1148283daca65115cd63642e60f/psycopg2-2.8.2.tar.gz
Installing collected packages: psycopg2
  Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.8.2
点击查看英文原文

What worked for me was the hint provided in the command to link openssl,

$ brew link openssl
Warning: Refusing to link macOS-provided software: openssl
If you need to have openssl first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl/bin:$PATH"' >> ~/.zshrc

For compilers to find openssl you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl/include"

$ export LDFLAGS="-L/usr/local/opt/openssl/lib"
$ export CPPFLAGS="-I/usr/local/opt/openssl/include"

$ pip install psycopg2
Collecting psycopg2
  Using cached https://files.pythonhosted.org/packages/23/7e/93c325482c328619870b6cd09370f6dbe1148283daca65115cd63642e60f/psycopg2-2.8.2.tar.gz
Installing collected packages: psycopg2
  Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.8.2

回答 4

在莫哈韦沙漠，我将它们添加到.bash_profile

export PATH="/usr/local/opt/openssl/bin:$PATH"
export LDFLAGS="-L/usr/local/opt/curl/lib -L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/curl/include -I/user/local/opt/openssl/include"

然后能够在python 3.7.4 virtualenv中安装psycopg 2.8.3。

重新安装xcode和命令行工具后。

以上所有答案都对您有所帮助！

点击查看英文原文

On mojave I added these to the .bash_profile

export PATH="/usr/local/opt/openssl/bin:$PATH"
export LDFLAGS="-L/usr/local/opt/curl/lib -L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/curl/include -I/user/local/opt/openssl/include"

was then able to install psycopg 2.8.3 in a python 3.7.4 virtualenv.

This after reinstalling xcode and the command line tools.

All the answers above helped!

回答 5

使用MacOS Catalina 10.15.4，以下是对我有用的唯一命令：

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2
点击查看英文原文

With MacOS Catalina 10.15.4, the following was the only command that worked for me:

env LDFLAGS="-I/usr/local/opt/openssl/include -L/usr/local/opt/openssl/lib" pip install psycopg2

回答 6

这是新的macOs版本的问题，其中pip无法安装cryptography。解决我的问题的是将env提供给install命令：

brew install openssl
env LDFLAGS="-L$(brew --prefix openssl)/lib" CFLAGS="-I$(brew --prefix openssl)/include" <YOUR COMMAND HERE>

您可以<YOUR COMMAND HERE>用pip install cryptography或替换pip install <SOMETHING THAT REQUIRES cryptography>。

归功于本文：修复macOS Sierra致命错误：找不到“ openssl / opensslv.h”或“ openssl / aes.h”文件

点击查看英文原文

This’s the problem of new macOs version, where pip cannot install cryptography. What fixed my problem is to provide the env to the install command:

brew install openssl
env LDFLAGS="-L$(brew --prefix openssl)/lib" CFLAGS="-I$(brew --prefix openssl)/include" <YOUR COMMAND HERE>

You can replace <YOUR COMMAND HERE> with pip install cryptography, or pip install <SOMETHING THAT REQUIRES cryptography> for example.

Credit to this article: Fixing macOS Sierra fatal error: ‘openssl/opensslv.h’ or ‘openssl/aes.h’ file not found

回答 7

在使用Homebrew安装OpenSSL之后，以下两个命令使用Fish帮我解决了此问题。

set -gx LDFLAGS "-L/usr/local/opt/openssl/lib"
set -gx CPPFLAGS "-I/usr/local/opt/openssl/include"

使用brew info openssl之后，可以获取最新信息。

点击查看英文原文

Using Fish, the following two commands solved this issue for me after installing OpenSSL with Homebrew.

set -gx LDFLAGS "-L/usr/local/opt/openssl/lib"
set -gx CPPFLAGS "-I/usr/local/opt/openssl/include"

Use brew info openssl to get up-to-date info.

回答 8

最近在High Sierra中出现了此问题，刚刚在virtualenv中安装了Python 3.7。

解决方案是使用更高版本的psycopg2。版本2.7.7有效，而版本2.7.1无效。

点击查看英文原文

Recently had this problem in High Sierra, having just installed Python 3.7 in a virtualenv.

The solution is to use a later version of psycopg2. Version 2.7.7 worked, where 2.7.1 did not.

回答 9

而不是同一作者的psycopg2install，install psycopg2-binary：

pip install psycopg2-binary

这是文档关于此PyPI软件包的说明：

您可以通过从PyPI安装psycopg2-binary软件包来获取不需要编译器或外部库的独立软件包：

$ pip install psycopg2-binary

二进制软件包是开发和测试的实际选择，但在生产中，建议使用从源构建的软件包。

点击查看英文原文

Instead of installing psycopg2, install psycopg2-binary, from the same authors:

pip install psycopg2-binary

This is what the documentation says about this PyPI package:

You can […] obtain a stand-alone package, not requiring a compiler or external libraries, by installing the psycopg2-binary package from PyPI:

$ pip install psycopg2-binary

The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources.

回答 10

我在莫哈韦沙漠上遇到这个问题。Mojave不会创建psycopg2需要安装的/ usr / include目录。这并不明显。我在这里找到解决方案： 如何从命令行更新Xcode，该命令行引用：https : //forums.developer.apple.com/thread/104296

点击查看英文原文

I was having this issue on Mojave. Mojave does not create a /usr/include directory, which psycopg2 needs to install. This was not obvious. I found the solution here: How to update Xcode from command line, which references: https://forums.developer.apple.com/thread/104296

回答 11

我安装了cython后遇到了同样的错误，并解决了这个问题

点击查看英文原文

I had this same error and got it to resolve after I pip installed cython

回答 12

从conda环境运行PyCharm，使用以下方法解决了我的问题：

--> conda install psycopg2
The following packages will be UPDATED: ...

...
Proceed ([y]/n)? 
--> y
--> pip3 install psycopg2
Installing collected packages: psycopg2
Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.8.4

'''
点击查看英文原文

Running PyCharm from conda environment, solved my issue using:

--> conda install psycopg2
The following packages will be UPDATED: ...

...
Proceed ([y]/n)? 
--> y
--> pip3 install psycopg2
Installing collected packages: psycopg2
Running setup.py install for psycopg2 ... done
Successfully installed psycopg2-2.8.4

'''

回答 13

我使用MacPorts安装了OpenSSL，因此目录与Brew不同。

sudo port install openssl

我通过执行以下操作找到了目录：

port contents openssl | grep lib
port contents openssl | grep include

然后我导出变量：

export LDFLAGS="-L/opt/local/lib"
export CPPFLAGS="-I/opt/local/include/openssl"

您可能还必须：

xcode-select --install
点击查看英文原文

I installed OpenSSL using MacPorts therefore directories are not like those of Brew.

sudo port install openssl

I found the directories by doing:

port contents openssl | grep lib
port contents openssl | grep include

Then I exported the variables:

export LDFLAGS="-L/opt/local/lib"
export CPPFLAGS="-I/opt/local/include/openssl"

You might also have to:

xcode-select --install

回答 14

export LIBRARY_PATH=$LIBRARY_PATH:/usr/local/opt/openssl/lib/

为我工作

点击查看英文原文

export LIBRARY_PATH=$LIBRARY_PATH:/usr/local/opt/openssl/lib/

worked for me

回答 15

我设法通过使用以下方法修复了该问题： 

brew unlink openssl && brew link openssl --force

我不确定这与之前尝试在OpenSSL上进行的brew卸载/升级有何不同。我的假设是，这些操作留下了一些“错误的”共享库，这些库无法正常工作。请注意，这还解决了安装python加密模块的问题。

点击查看英文原文

I’ve managed to fix it by using: 

brew unlink openssl && brew link openssl --force

I am not sure how this differs from the brew uninstall/upgrades that I did on OpenSSL in prior attempts I’ve made. My assumption is that these operations left some of the “faulty” shared libraries which were preventing this from working. Note that this also fixed issues with installing python cryptography module.

知识问答

如何在Django中设置PostgreSQL数据库

问题：如何在Django中设置PostgreSQL数据库

我是Python和Django的新手。

我正在使用PostgreSQL数据库引擎后端配置Django项目，但是每个数据库操作都出现错误。例如，当我跑步时manage.py syncdb，我得到：

C:\xampp\htdocs\djangodir>python manage.py syncdb
Traceback (most recent call last):
  File "manage.py", line 11, in <module>
    execute_manager(settings)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
438, in execute_manager
    utility.execute()
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
261, in fetch_command
    klass = load_command_class(app_name, subcommand)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
67, in load_command_class
    module = import_module('%s.management.commands.%s' % (app_name, name))
  File "C:\Python27\lib\site-packages\django\utils\importlib.py", line 35, in im
port_module
    __import__(name)
  File "C:\Python27\lib\site-packages\django\core\management\commands\syncdb.py"
, line 7, in <module>
    from django.core.management.sql import custom_sql_for_model, emit_post_sync_
signal
  File "C:\Python27\lib\site-packages\django\core\management\sql.py", line 6, in
 <module>
    from django.db import models
  File "C:\Python27\lib\site-packages\django\db\__init__.py", line 77, in <modul
e>
    connection = connections[DEFAULT_DB_ALIAS]
  File "C:\Python27\lib\site-packages\django\db\utils.py", line 92, in __getitem
__
    backend = load_backend(db['ENGINE'])
  File "C:\Python27\lib\site-packages\django\db\utils.py", line 33, in load_back
end
    return import_module('.base', backend_name)
  File "C:\Python27\lib\site-packages\django\utils\importlib.py", line 35, in im
port_module
    __import__(name)
  File "C:\Python27\lib\site-packages\django\db\backends\postgresql\base.py", li
ne 23, in <module>
    raise ImproperlyConfigured("Error loading psycopg module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading psycopg module: No mo
dule named psycopg

有人可以告诉我发生了什么吗？

点击查看英文原文

I’m new to Python and Django.

I’m configuring a Django project using a PostgreSQL database engine backend, But I’m getting errors on each database operation. For example when I run manage.py syncdb, I’m getting:

C:\xampp\htdocs\djangodir>python manage.py syncdb
Traceback (most recent call last):
  File "manage.py", line 11, in <module>
    execute_manager(settings)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
438, in execute_manager
    utility.execute()
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
261, in fetch_command
    klass = load_command_class(app_name, subcommand)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line
67, in load_command_class
    module = import_module('%s.management.commands.%s' % (app_name, name))
  File "C:\Python27\lib\site-packages\django\utils\importlib.py", line 35, in im
port_module
    __import__(name)
  File "C:\Python27\lib\site-packages\django\core\management\commands\syncdb.py"
, line 7, in <module>
    from django.core.management.sql import custom_sql_for_model, emit_post_sync_
signal
  File "C:\Python27\lib\site-packages\django\core\management\sql.py", line 6, in
 <module>
    from django.db import models
  File "C:\Python27\lib\site-packages\django\db\__init__.py", line 77, in <modul
e>
    connection = connections[DEFAULT_DB_ALIAS]
  File "C:\Python27\lib\site-packages\django\db\utils.py", line 92, in __getitem
__
    backend = load_backend(db['ENGINE'])
  File "C:\Python27\lib\site-packages\django\db\utils.py", line 33, in load_back
end
    return import_module('.base', backend_name)
  File "C:\Python27\lib\site-packages\django\utils\importlib.py", line 35, in im
port_module
    __import__(name)
  File "C:\Python27\lib\site-packages\django\db\backends\postgresql\base.py", li
ne 23, in <module>
    raise ImproperlyConfigured("Error loading psycopg module: %s" % 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?

回答 0

您需要安装psycopg2Python库。

安装

下载http://initd.org/psycopg/，然后将其安装在Python PATH下

下载后，轻松解压缩tarball并：

$ python setup.py install

或者，如果愿意，可以通过easy_install或pip进行安装。

（我更喜欢无缘无故地使用pip而不是easy_install。）

  • $ easy_install psycopg2
  • $ pip install psycopg2

组态

在设置 .py中

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'db_name',                      
        'USER': 'db_user',
        'PASSWORD': 'db_user_password',
        'HOST': '',
        'PORT': 'db_port_number',
    }
}

-其他安装说明可在下载页面和安装页面找到。

点击查看英文原文

You need to install psycopg2 Python library.

Installation

Download http://initd.org/psycopg/, then install it under Python PATH

After downloading, easily extract the tarball and:

$ python setup.py install

Or if you wish, install it by either easy_install or pip.

(I prefer to use pip over easy_install for no reason.)

  • $ easy_install psycopg2
  • $ pip install psycopg2

Configuration

in settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'db_name',                      
        'USER': 'db_user',
        'PASSWORD': 'db_user_password',
        'HOST': '',
        'PORT': 'db_port_number',
    }
}

– Other installation instructions can be found at download page and install page.

回答 1

另外，请确保已安装PostgreSQL开发包。在Ubuntu上，您需要执行以下操作：

$ sudo apt-get install libpq-dev
点击查看英文原文

Also make sure you have the PostgreSQL development package installed. On Ubuntu you need to do something like this:

$ sudo apt-get install libpq-dev

回答 2

我使用的分步指南：

 - sudo apt-get install python-dev
 - sudo apt-get install postgresql-server-dev-9.1
 - sudo apt-get install python-psycopg2 - Or sudo pip install psycopg2

您可能需要安装图形工具来管理数据库，为此，您可以执行以下操作：

sudo apt-get install postgresql pgadmin3

之后，您必须更改Postgre用户密码，然后执行以下操作：

 - sudo su
 - su postgres -c psql postgres
 - ALTER USER postgres WITH PASSWORD 'YourPassWordHere';
 - \q

在您的settings.py文件中，您可以执行以下操作：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '',
        'PORT': '',
    }
}

额外：

如果要使用命令行创建数据库，则可以执行以下操作：

- sudo su
- su postgres -c psql postgres
- CREATE DATABASE dbname;
- CREATE USER djangouser WITH ENCRYPTED PASSWORD 'myPasswordHere';
- GRANT ALL PRIVILEGES ON DATABASE dbname TO djangouser;

在您的settings.py文件中，您可以执行以下操作：

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'djangouser',
        'PASSWORD': 'myPasswordHere',
        'HOST': '',
        'PORT': '',
    }
}
点击查看英文原文

Step by step that I use:

 - sudo apt-get install python-dev
 - sudo apt-get install postgresql-server-dev-9.1
 - sudo apt-get install python-psycopg2 - Or sudo pip install psycopg2

You may want to install a graphic tool to manage your databases, for that you can do:

sudo apt-get install postgresql pgadmin3

After, you must change Postgre user password, then do:

 - sudo su
 - su postgres -c psql postgres
 - ALTER USER postgres WITH PASSWORD 'YourPassWordHere';
 - \q

On your settings.py file you do:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '',
        'PORT': '',
    }
}

Extra:

If you want to create the db using the command line you can just do:

- sudo su
- su postgres -c psql postgres
- CREATE DATABASE dbname;
- CREATE USER djangouser WITH ENCRYPTED PASSWORD 'myPasswordHere';
- GRANT ALL PRIVILEGES ON DATABASE dbname TO djangouser;

On your settings.py file you do:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'djangouser',
        'PASSWORD': 'myPasswordHere',
        'HOST': '',
        'PORT': '',
    }
}

回答 3

这似乎有点冗长，但是对我来说没有任何错误。

首先，从Ubuntu软件中心安装phppgadmin。

然后在终端中运行这些步骤。

sudo apt-get install libpq-dev python-dev
pip install psycopg2
sudo apt-get install postgresql postgresql-contrib phppgadmin

启动apache服务器

sudo service apache2 start

现在在终端中也运行此命令以编辑apache文件。

sudo gedit /etc/apache2/apache2.conf

将以下行添加到打开的文件中：

Include /etc/apache2/conf.d/phppgadmin

现在重新加载apache。使用终端。

sudo /etc/init.d/apache2 reload

现在，您将不得不创建一个新的数据库。以“ postgres”用户身份登录。在终端继续。

sudo su - postgres

如果您在使用密码“ postgres”时遇到麻烦，可以在此处使用答案https://stackoverflow.com/a/12721020/1990793进行更改，然后继续执行步骤。

现在创建一个数据库

createdb <db_name>

现在，创建一个新用户以稍后登录到phppgadmin，并提供一个新密码。

createuser -P <new_user>

现在您的postgressql已设置完毕，您可以转到：

http://localhost/phppgadmin/

并使用您创建的新用户登录，以查看数据库。

点击查看英文原文

This may seem a bit lengthy, but it worked for me without any error.

At first, Install phppgadmin from Ubuntu Software Center.

Then run these steps in terminal.

sudo apt-get install libpq-dev python-dev
pip install psycopg2
sudo apt-get install postgresql postgresql-contrib phppgadmin

Start the apache server

sudo service apache2 start

Now run this too in terminal, to edit the apache file.

sudo gedit /etc/apache2/apache2.conf

Add the following line to the opened file:

Include /etc/apache2/conf.d/phppgadmin

Now reload apache. Use terminal.

sudo /etc/init.d/apache2 reload

Now you will have to create a new database. Login as ‘postgres’ user. Continue in terminal.

sudo su - postgres

In case you have trouble with the password of ‘postgres’, you can change it using the answer here https://stackoverflow.com/a/12721020/1990793 and continue with the steps.

Now create a database

createdb <db_name>

Now create a new user to login to phppgadmin later, providing a new password.

createuser -P <new_user>

Now your postgressql has been setup, and you can go to:

http://localhost/phppgadmin/

and login using the new user you’ve created, in order to view the database.

回答 4

您可以使用以下命令安装“ psycopg”：

# sudo easy_install psycopg2

另外，您可以使用pip：

# pip install psycopg2

easy_install和pip包含在ActivePython中，或从各个 项目站点手动安装。

或者，只需获取预构建的Windows安装程序。

点击查看英文原文

You can install “psycopg” with the following command:

# sudo easy_install psycopg2

Alternatively, you can use pip :

# pip install psycopg2

easy_install and pip are included with ActivePython, or manually installed from the respective project sites.

Or, simply get the pre-built Windows installer.

回答 5

眼前的问题似乎是您缺少了psycopg模块。

点击查看英文原文

The immediate problem seems to be that you’re missing the psycopg module.

回答 6

如果您使用的是Fedora 20，Django 1.6.5，postgresql 9.3。*，并且需要psycopg2模块，请执行以下操作：

yum install postgresql-devel
easy_install psycopg2

如果您像我一样，可能会找不到有据可查的libpq-dev rpm。

点击查看英文原文

If you are using Fedora 20, Django 1.6.5, postgresql 9.3.* and you need the psycopg2 module, do this:

yum install postgresql-devel
easy_install psycopg2

If you are like me, you may have trouble finding the well documented libpq-dev rpm… The above worked for me just now.

回答 7

我在Mac上遇到过同样的问题。

解决方案是仅使用PIP来安装所有东西，并触摸一些东西。

首先从以下 网址安装PIP：https ：//pip.pypa.io/en/latest/

然后，您要确定pg_config的路径是否在PATH中（回显$ PATH），如果没有，则可以编辑bash_profile：

vi /Users/<user>/.bash_profile

并添加以下行：

export PATH=$PATH:/path/to/pg_config/bin

如果您不知道pg_config在哪里，则可以使用“ locate”工具，但是请确保您的locate.db是最新的（我使用的是旧的locate.db，并且使用的路径不存在）。

sudo /usr/libexec/locate.updatedb
locate pg_config

然后安装Django（如果需要）和psycopg2。

sudo pip install Django
sudo pip install psycopg2

然后在settings.py（localhost：defaultport）中

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '',
        'PORT': '',
    }
}

问候！

点击查看英文原文

I was having the same Issue on Mac.

The solution was to use only PIP to install everything, and touch some things.

First install PIP from: https://pip.pypa.io/en/latest/

Then you want to make sure if path to pg_config is in your PATH (echo $PATH), if not you can edit your bash_profile:

vi /Users/<user>/.bash_profile

and add this line:

export PATH=$PATH:/path/to/pg_config/bin

If you don’t know where pg_config is you can use the “locate” tool, but be sure your locate.db is up to date (i was using an old locate.db and using paths that does not exists).

sudo /usr/libexec/locate.updatedb
locate pg_config

Then install Django (if needed) and psycopg2.

sudo pip install Django
sudo pip install psycopg2

And then in settings.py (localhost:defaultport)

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dbname',
        'USER': 'postgres',
        'PASSWORD': 'postgres',
        'HOST': '',
        'PORT': '',
    }
}

Greets!

回答 8

$ sudo apt-get install libpq-dev

年，这解决了我的问题。执行此操作后，请执行以下操作：pip install psycopg2

点击查看英文原文 
$ sudo apt-get install libpq-dev

Year, this solve my problem. After execute this, do: pip install psycopg2

回答 9

请注意，psycopg2通过pip或setup.py进行安装需要具有Visual Studio 2008（更确切地说是可执行文件vcvarsall.bat）。如果您没有管理员权限来安装它或在Windows上设置适当的PATH变量，则可以从此处下载已编译的库。

点击查看英文原文

Please note that installation of psycopg2 via pip or setup.py requires to have Visual Studio 2008 (more precisely executable file vcvarsall.bat). If you don’t have admin rights to install it or set the appropriate PATH variable on Windows, you can download already compiled library from here.

回答 10

这是PostgreSQL在ubuntu服务器中设置的非常好的逐步过程之一。我已经尝试了它，Ubuntu 16.04并且可以正常工作。

https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application-on-ubuntu-14-04

点击查看英文原文

This is one of the very good and step by step process to set up PostgreSQL in ubuntu server. I have tried it with Ubuntu 16.04 and its working.

https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application-on-ubuntu-14-04

知识问答

Python / postgres / psycopg2：获取刚刚插入的行的ID

问题：Python / postgres / psycopg2：获取刚刚插入的行的ID

我正在使用Python和psycopg2连接到Postgres。

当我插入一行时…

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

…如何获取刚插入的行的ID？试：

hundred = cursor.fetchall()

使用时返回错误RETURNING id：

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ") RETURNING id;"
hundred = cursor.execute(sql_string)

简单地返回None。

更新：也是currval（即使直接在postgres中使用此命令也可以）：

sql_string = "SELECT currval(pg_get_serial_sequence('hundred', 'id'));"
hundred_id = cursor.execute(sql_string)

有人可以建议吗？

谢谢！

点击查看英文原文

I’m using Python and psycopg2 to interface to postgres.

When I insert a row…

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

… how do I get the ID of the row I’ve just inserted? Trying:

hundred = cursor.fetchall()

returns an error, while using RETURNING id:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES ("
sql_string += hundred_name + ", '" + hundred_slug + "', " + status + ") RETURNING id;"
hundred = cursor.execute(sql_string)

simply returns None.

UPDATE: So does currval (even though using this command directly into postgres works): 

sql_string = "SELECT currval(pg_get_serial_sequence('hundred', 'id'));"
hundred_id = cursor.execute(sql_string)

Can anyone advise?

thanks!

回答 0

cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]

并且，请不要手动构建包含值的SQL字符串。您可以（并且应该！）分别传递值，从而不必进行转义和SQL注入：

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;"
cursor.execute(sql_string, (hundred_name, hundred_slug, status))
hundred = cursor.fetchone()[0]

有关更多详细信息，请参见psycopg文档：http ://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

点击查看英文原文 
cursor.execute("INSERT INTO .... RETURNING id")
id_of_new_row = cursor.fetchone()[0]

And please do not build SQL strings containing values manually. You can (and should!) pass values separately, making it unnecessary to escape and SQL injection impossible:

sql_string = "INSERT INTO domes_hundred (name,name_slug,status) VALUES (%s,%s,%s) RETURNING id;"
cursor.execute(sql_string, (hundred_name, hundred_slug, status))
hundred = cursor.fetchone()[0]

See the psycopg docs for more details: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

回答 1

我到这里结束是因为我有一个类似的问题，但是我们使用的是Postgres-XC，它尚不支持RETURNING ID子句。在这种情况下，您可以使用：

cursor.execute（'INSERT INTO ........'）
cursor.execute（'SELECT LASTVAL（）'）
lastid = cursor.fetchone（）['lastval']

以防万一对任何人都有用！

点击查看英文原文

I ended up here because I had a similar problem, but we’re using Postgres-XC, which doesn’t yet support the RETURNING ID clause. In that case you can use:

cursor.execute('INSERT INTO ........')
cursor.execute('SELECT LASTVAL()')
lastid = cursor.fetchone()['lastval']

Just in case it was useful for anyone!

回答 2

考虑一个RETURNING子句http://www.postgresql.org/docs/8.3/static/sql-insert.html

点击查看英文原文

Consider a RETURNING clause http://www.postgresql.org/docs/8.3/static/sql-insert.html

知识问答

psycopg2：通过一个查询插入多行

问题：psycopg2：通过一个查询插入多行

我需要用一个查询插入多行（行数不是常数），所以我需要像这样执行查询：

INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

我知道的唯一方法是

args = [(1,2), (3,4), (5,6)]
args_str = ','.join(cursor.mogrify("%s", (x, )) for x in args)
cursor.execute("INSERT INTO t (a, b) VALUES "+args_str)

但我想要一些更简单的方法。

点击查看英文原文

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 = ','.join(cursor.mogrify("%s", (x, )) for x in args)
cursor.execute("INSERT INTO t (a, b) VALUES "+args_str)

but I want some simpler way.

回答 0

我构建了一个程序，该程序将多行插入到位于另一个城市的服务器上。

我发现使用这种方法的速度大约是10倍executemany。就我而言，tup是一个包含约2000行的元组。使用此方法大约花了10秒钟：

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

使用此方法需要2分钟：

cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup)
点击查看英文原文

I built a program that inserts multiple lines to a server that was located in another city.

I found out that using this method was about 10 times faster than executemany. In my case tup is a tuple containing about 2000 rows. It took about 10 seconds when using this method:

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

and 2 minutes when using this method:

cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup)

回答 1

Psycopg 2.7中的新execute_values方法：

data = [(1,'x'), (2,'y')]
insert_query = 'insert into t (a, b) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, data, template=None, page_size=100
)

在Psycopg 2.6中执行此操作的pythonic方法：

data = [(1,'x'), (2,'y')]
records_list_template = ','.join(['%s'] * len(data))
insert_query = 'insert into t (a, b) values {}'.format(records_list_template)
cursor.execute(insert_query, data)

说明：如果要插入的数据作为元组列表给出，例如

data = [(1,'x'), (2,'y')]

那么它已经是确切要求的格式了

  1. 该子句的values语法insert需要一个记录列表，如

    insert into t (a, b) values (1, 'x'),(2, 'y')

  2. Psycopg使Python适应tuplePostgresql record。

唯一必要的工作是提供一个由psycopg填充的记录列表模板

# We use the data list to be sure of the template length
records_list_template = ','.join(['%s'] * len(data))

并将其放在insert查询中

insert_query = 'insert into t (a, b) values {}'.format(records_list_template)

打印insert_query输出

insert into t (a, b) values %s,%s

现在到通常的Psycopg参数替换

cursor.execute(insert_query, data)

或者只是测试将要发送到服务器的内容

print (cursor.mogrify(insert_query, data).decode('utf8'))

输出：

insert into t (a, b) values (1, 'x'),(2, 'y')
点击查看英文原文

New execute_values method in Psycopg 2.7:

data = [(1,'x'), (2,'y')]
insert_query = 'insert into t (a, b) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, data, template=None, page_size=100
)

The pythonic way of doing it in Psycopg 2.6:

data = [(1,'x'), (2,'y')]
records_list_template = ','.join(['%s'] * len(data))
insert_query = 'insert into t (a, b) values {}'.format(records_list_template)
cursor.execute(insert_query, data)

Explanation: If the data to be inserted is given as a list of tuples like in

data = [(1,'x'), (2,'y')]

then it is already in the exact required format as

  1. the values syntax of the insert clause expects a list of records as in

    insert into t (a, b) values (1, 'x'),(2, 'y')

  2. Psycopg adapts a Python tuple to a Postgresql record.

The only necessary work is to provide a records list template to be filled by psycopg

# We use the data list to be sure of the template length
records_list_template = ','.join(['%s'] * len(data))

and place it in the insert query

insert_query = 'insert into t (a, b) values {}'.format(records_list_template)

Printing the insert_query outputs

insert into t (a, b) values %s,%s

Now to the usual Psycopg arguments substitution

cursor.execute(insert_query, data)

Or just testing what will be sent to the server

print (cursor.mogrify(insert_query, data).decode('utf8'))

Output:

insert into t (a, b) values (1, 'x'),(2, 'y')

回答 2

使用psycopg2 2.7更新：

如executemany()以下线程中所述，经典版本比@ ant32的实现（称为“折叠”）慢约60倍：https ://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib.com

此实现已在2.7版中添加到psycopg2中，称为execute_values()：

from psycopg2.extras import execute_values
execute_values(cur,
    "INSERT INTO test (id, v1, v2) VALUES %s",
    [(1, 2, 3), (4, 5, 6), (7, 8, 9)])

上一个答案：

要插入多行，使用multirow VALUES语法execute()比使用psycopg2快约10倍executemany()。确实，executemany()只是运行许多单独的INSERT语句。

@ ant32的代码在Python 2中可以完美地工作。但是在Python 3中，cursor.mogrify()返回字节，cursor.execute()采用字节或字符串并','.join()需要str实例。

因此，在Python 3中，您可能需要通过添加.decode('utf-8')以下内容来修改@ ant32的代码：

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x).decode('utf-8') for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

或仅使用字节（带有b''或b""）： 

args_bytes = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute(b"INSERT INTO table VALUES " + args_bytes)
点击查看英文原文

Update with psycopg2 2.7:

The classic executemany() is about 60 times slower than @ant32 ‘s implementation (called “folded”) as explained in this thread: https://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib.com

This implementation was added to psycopg2 in version 2.7 and is called execute_values():

from psycopg2.extras import execute_values
execute_values(cur,
    "INSERT INTO test (id, v1, v2) VALUES %s",
    [(1, 2, 3), (4, 5, 6), (7, 8, 9)])

Previous Answer:

To insert multiple rows, using the multirow VALUES syntax with execute() is about 10x faster than using psycopg2 executemany(). Indeed, executemany() just runs many individual INSERT statements.

@ant32 ‘s code works perfectly in Python 2. But in Python 3, cursor.mogrify() returns bytes, cursor.execute() takes either bytes or strings, and ','.join() expects str instance.

So in Python 3 you may need to modify @ant32 ‘s code, by adding .decode('utf-8'):

args_str = ','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x).decode('utf-8') for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

Or by using bytes (with b'' or b"") only: 

args_bytes = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute(b"INSERT INTO table VALUES " + args_bytes)

回答 3

cursor.copy_from是迄今为止我发现的用于批量插入的最快解决方案。这是我编写的要点，其中包含一个名为IteratorFile的类，该类允许迭代器产生像文件一样读取字符串。我们可以使用生成器表达式将每个输入记录转换为字符串。所以解决方案是

args = [(1,2), (3,4), (5,6)]
f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args))
cursor.copy_from(f, 'table_name', columns=('a', 'b'))

对于这个琐碎的args来说，速度差异不会太大，但是在处理数千行时，我看到了很大的加速。与构建巨大的查询字符串相比，它还将提高内存效率。迭代器一次只能在内存中保存一个输入记录，在某个时候，通过构建查询字符串，您的Python进程或Postgres中的内存将用完。

点击查看英文原文

cursor.copy_from is the fastest solution I’ve found for bulk inserts by far. Here’s a gist I made containing a class named IteratorFile which allows an iterator yielding strings to be read like a file. We can convert each input record to a string using a generator expression. So the solution would be

args = [(1,2), (3,4), (5,6)]
f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args))
cursor.copy_from(f, 'table_name', columns=('a', 'b'))

For this trivial size of args it won’t make much of a speed difference, but I see big speedups when dealing with thousands+ of rows. It will also be more memory efficient than building a giant query string. An iterator would only ever hold one input record in memory at a time, where at some point you’ll run out of memory in your Python process or in Postgres by building the query string.

回答 4

来自Postgresql.org的 Psycopg2的教程页面的摘要（请参见底部）：

我想向您展示的最后一项是如何使用字典插入多行。如果您具有以下条件：

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

您可以使用以下命令轻松地将所有三行插入字典中： 

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

它不会节省太多代码，但是绝对可以看起来更好。

点击查看英文原文

A snippet from Psycopg2’s tutorial page at Postgresql.org (see bottom):

A last item I would like to show you is how to insert multiple rows using a dictionary. If you had the following: 

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

You could easily insert all three rows within the dictionary by using: 

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

It doesn’t save much code, but it definitively looks better.

回答 5

所有这些技术在Postgres术语中都称为“扩展插入”，截至2016年11月24日，它仍然比psychopg2的executemany（）和该线程中列出的所有其他方法快了很多（我在尝试此方法之前曾尝试过）回答）。

这是一些不使用cur.mogrify的代码，它很不错，只是可以帮助您：

valueSQL = [ '%s', '%s', '%s', ... ] # as many as you have columns.
sqlrows = []
rowsPerInsert = 3 # more means faster, but with diminishing returns..
for row in getSomeData:
        # row == [1, 'a', 'yolo', ... ]
        sqlrows += row
        if ( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0:
                # sqlrows == [ 1, 'a', 'yolo', 2, 'b', 'swag', 3, 'c', 'selfie' ]
                insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert)
                cur.execute(insertSQL, sqlrows)
                con.commit()
                sqlrows = []
insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*len(sqlrows))
cur.execute(insertSQL, sqlrows)
con.commit()

但应注意，如果可以使用copy_from（），则应该使用copy_from;）

点击查看英文原文

All of these techniques are called ‘Extended Inserts” in Postgres terminology, and as of the 24th of November 2016, it’s still a ton faster than psychopg2’s executemany() and all the other methods listed in this thread (which i tried before coming to this answer).

Here’s some code which doesnt use cur.mogrify and is nice and simply to get your head around:

valueSQL = [ '%s', '%s', '%s', ... ] # as many as you have columns.
sqlrows = []
rowsPerInsert = 3 # more means faster, but with diminishing returns..
for row in getSomeData:
        # row == [1, 'a', 'yolo', ... ]
        sqlrows += row
        if ( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0:
                # sqlrows == [ 1, 'a', 'yolo', 2, 'b', 'swag', 3, 'c', 'selfie' ]
                insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert)
                cur.execute(insertSQL, sqlrows)
                con.commit()
                sqlrows = []
insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*len(sqlrows))
cur.execute(insertSQL, sqlrows)
con.commit()

But it should be noted that if you can use copy_from(), you should use copy_from ;)

回答 6

几年来我一直在使用ant32的答案。但是我发现这在python 3中解决了一个错误，因为它mogrify返回了一个字节字符串。

显式转换为bytse字符串是使代码与python 3兼容的简单解决方案。

args_str = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) 
cur.execute(b"INSERT INTO table VALUES " + args_str)
点击查看英文原文

I’ve been using ant32’s answer above for several years. However I’ve found that is thorws an error in python 3 because mogrify returns a byte string.

Converting explicitly to bytse strings is a simple solution for making code python 3 compatible.

args_str = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) 
cur.execute(b"INSERT INTO table VALUES " + args_str)

回答 7

另一种有效的好方法-将要插入的行作为1个参数传递，它是json对象的数组。

例如，您传递的参数：

[ {id: 18, score: 1}, { id: 19, score: 5} ]

它是数组，其中可以包含任意数量的对象。然后您的SQL看起来像：

INSERT INTO links (parent_id, child_id, score) 
SELECT 123, (r->>'id')::int, (r->>'score')::int 
FROM unnest($1::json[]) as r

注意：您的postgress必须足够新，才能支持json

点击查看英文原文

Another nice and efficient approach – is to pass rows for insertion as 1 argument, which is array of json objects.

E.g. you passing argument:

[ {id: 18, score: 1}, { id: 19, score: 5} ]

It is array, which may contain any amount of objects inside. Then your SQL looks like:

INSERT INTO links (parent_id, child_id, score) 
SELECT 123, (r->>'id')::int, (r->>'score')::int 
FROM unnest($1::json[]) as r

Notice: Your postgress must be new enough, to support json

回答 8

上面（https://stackoverflow.com/a/30721460/11100064）的@ jopseph.sheedy （https://stackoverflow.com/users/958118/joseph-sheedy）提供的cursor.copyfrom解决方案确实快如闪电。

但是，他给出的示例不适用于具有任意多个字段的记录，因此我花了一些时间才弄清楚如何正确使用它。

IteratorFile需要使用这样的制表符分隔的字段实例化（r是一个字典列表，其中每个字典是一条记录）： 

    f = IteratorFile("{0}\t{1}\t{2}\t{3}\t{4}".format(r["id"],
        r["type"],
        r["item"],
        r["month"],
        r["revenue"]) for r in records)

为了概括任意数量的字段，我们将首先创建一个带有正确数量的制表符和字段占位符的行字符串："{}\t{}\t{}....\t{}"然后使用.format()来填充字段值 *list(r.values())) for r in records：：

        line = "\t".join(["{}"] * len(records[0]))

        f = IteratorFile(line.format(*list(r.values())) for r in records)

完整的功能在这里。

点击查看英文原文

The cursor.copyfrom solution as provided by @jopseph.sheedy (https://stackoverflow.com/users/958118/joseph-sheedy) above (https://stackoverflow.com/a/30721460/11100064) is indeed lightning fast.

However, the example he gives are not generically usable for a record with any number of fields and it took me while to figure out how to use it correctly.

The IteratorFile needs to be instantiated with tab-separated fields like this (r is a list of dicts where each dict is a record): 

    f = IteratorFile("{0}\t{1}\t{2}\t{3}\t{4}".format(r["id"],
        r["type"],
        r["item"],
        r["month"],
        r["revenue"]) for r in records)

To generalise for an arbitrary number of fields we will first create a line string with the correct amount of tabs and field placeholders : "{}\t{}\t{}....\t{}" and then use .format() to fill in the field values : *list(r.values())) for r in records:

        line = "\t".join(["{}"] * len(records[0]))

        f = IteratorFile(line.format(*list(r.values())) for r in records)

complete function in gist here.

回答 9

如果您使用的是SQLAlchemy，则无需手工处理字符串，因为SQLAlchemy 支持VALUES为单个INSERT语句生成多行子句：

rows = []
for i, name in enumerate(rawdata):
    row = {
        'id': i,
        'name': name,
        'valid': True,
    }
    rows.append(row)
if len(rows) > 0:  # INSERT fails if no rows
    insert_query = SQLAlchemyModelName.__table__.insert().values(rows)
    session.execute(insert_query)
点击查看英文原文

If you’re using SQLAlchemy, you don’t need to mess with hand-crafting the string because SQLAlchemy supports generating a multi-row VALUES clause for a single INSERT statement:

rows = []
for i, name in enumerate(rawdata):
    row = {
        'id': i,
        'name': name,
        'valid': True,
    }
    rows.append(row)
if len(rows) > 0:  # INSERT fails if no rows
    insert_query = SQLAlchemyModelName.__table__.insert().values(rows)
    session.execute(insert_query)

回答 10

自发布此问题以来，execute_batch已添加到psycopg2。

它比execute_values慢，但使用起来更简单。

点击查看英文原文

execute_batch has been added to psycopg2 since this question was posted.

It is slower than execute_values but simpler to use.

回答 11

executemany接受元组数组

https://www.postgresqltutorial.com/postgresql-python/insert/

    """ array of tuples """
    vendor_list = [(value1,)]

    """ insert multiple vendors into the vendors table  """
    sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
    conn = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.executemany(sql,vendor_list)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
点击查看英文原文

executemany accept array of tuples

https://www.postgresqltutorial.com/postgresql-python/insert/

    """ array of tuples """
    vendor_list = [(value1,)]

    """ insert multiple vendors into the vendors table  """
    sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
    conn = None
    try:
        # read database configuration
        params = config()
        # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
        # create a new cursor
        cur = conn.cursor()
        # execute the INSERT statement
        cur.executemany(sql,vendor_list)
        # commit the changes to the database
        conn.commit()
        # close communication with the database
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

回答 12

如果要在一个插入状态表中插入多行（假设您未使用ORM），那么到目前为止，对我来说最简单的方法是使用词典列表。这是一个例子：

 t = [{'id':1, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 6},
      {'id':2, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 7},
      {'id':3, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 8}]

conn.execute("insert into campaign_dates
             (id, start_date, end_date, campaignid) 
              values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);",
             t)

如您所见，将仅执行一个查询：

INFO sqlalchemy.engine.base.Engine insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);
INFO sqlalchemy.engine.base.Engine [{'campaignid': 6, 'id': 1, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 7, 'id': 2, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 8, 'id': 3, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}]
INFO sqlalchemy.engine.base.Engine COMMIT
点击查看英文原文

If you want to insert multiple rows within one insert statemens (assuming you are not using ORM) the easiest way so far for me would be to use list of dictionaries. Here is an example:

 t = [{'id':1, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 6},
      {'id':2, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 7},
      {'id':3, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 8}]

conn.execute("insert into campaign_dates
             (id, start_date, end_date, campaignid) 
              values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);",
             t)

As you can see only one query will be executed:

INFO sqlalchemy.engine.base.Engine insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);
INFO sqlalchemy.engine.base.Engine [{'campaignid': 6, 'id': 1, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 7, 'id': 2, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 8, 'id': 3, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}]
INFO sqlalchemy.engine.base.Engine COMMIT

回答 13

使用aiopg-下面的代码片段效果很好

    # items = [10, 11, 12, 13]
    # group = 1
    tup = [(gid, pid) for pid in items]
    args_str = ",".join([str(s) for s in tup])
    # insert into group values (1, 10), (1, 11), (1, 12), (1, 13)
    yield from cur.execute("INSERT INTO group VALUES " + args_str)
点击查看英文原文

Using aiopg – The snippet below works perfectly fine

    # items = [10, 11, 12, 13]
    # group = 1
    tup = [(gid, pid) for pid in items]
    args_str = ",".join([str(s) for s in tup])
    # insert into group values (1, 10), (1, 11), (1, 12), (1, 13)
    yield from cur.execute("INSERT INTO group VALUES " + args_str)

回答 14

最终，在SQLalchemy1.2版本中，此新实现被添加为在使用use_batch_mode = True初始化引擎时使用psycopg2.extras.execute_batch（）而不是executemany，例如：

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

然后，将不得不使用SQLalchmey的人不会费心尝试sqla和psycopg2的不同组合并直接将SQL一起使用。

点击查看英文原文

Finally in SQLalchemy1.2 version, this new implementation is added to use psycopg2.extras.execute_batch() instead of executemany when you initialize your engine with use_batch_mode=True like:

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

Then someone would have to use SQLalchmey won’t bother to try different combinations of sqla and psycopg2 and direct SQL together..

知识问答

DatabaseError：当前事务中止，命令被忽略，直到事务块结束？

问题：DatabaseError：当前事务中止，命令被忽略，直到事务块结束？

消息有很多错误：

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

从python-psycopg更改为python-psycopg2作为Django项目的数据库引擎。

代码保持不变，只是不知道这些错误来自何处。

点击查看英文原文

I got a lot of errors with the message :

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

after changed from python-psycopg to python-psycopg2 as Django project’s database engine.

The code remains the same, just don’t know where those errors are from.

回答 0

当查询产生错误并且您尝试运行另一个查询而不先回滚事务时，这就是postgres所做的。（您可能会认为这是一项安全功能，可以防止您破坏数据。）

要解决此问题，您将需要弄清楚错误查询在代码中的何处执行。在您的PostgreSQL服务器中使用log_statement和log_min_error_statement选项可能会有所帮助。

点击查看英文原文

This is what postgres does when a query produces an error and you try to run another query without first rolling back the transaction. (You might think of it as a safety feature, to keep you from corrupting your data.)

To fix this, you’ll want to figure out where in the code that bad query is being executed. It might be helpful to use the log_statement and log_min_error_statement options in your postgresql server.

回答 1

要消除错误，请在修复代码后回滚上一个（错误的）事务：

from django.db import transaction
transaction.rollback()

您可以使用try-except来防止发生错误：

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    transaction.rollback()

参考：Django文档

点击查看英文原文

To get rid of the error, roll back the last (erroneous) transaction after you’ve fixed your code:

from django.db import transaction
transaction.rollback()

You can use try-except to prevent the error from occurring:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    transaction.rollback()

Refer : Django documentation

回答 2

因此，我遇到了同样的问题。我在这里遇到的问题是我的数据库未正确同步。简单的问题似乎总是引起最大的忧虑。

要在终端的应用程序目录中同步django db，请输入：

$ python manage.py syncdb

编辑：请注意，如果您使用的是django-south，则运行’$ python manage.py migration’命令也可以解决此问题。

祝您编码愉快！

点击查看英文原文

So, I ran into this same issue. The problem I was having here was that my database wasn’t properly synced. Simple problems always seem to cause the most angst…

To sync your django db, from within your app directory, within terminal, type:

$ python manage.py syncdb

Edit: Note that if you are using django-south, running the ‘$ python manage.py migrate’ command may also resolve this issue.

Happy coding!

回答 3

在Flask中，您只需要编写：

curs = conn.cursor()
curs.execute("ROLLBACK")
conn.commit()

PS文档在这里https://www.postgresql.org/docs/9.4/static/sql-rollback.html

点击查看英文原文

In Flask you just need to write:

curs = conn.cursor()
curs.execute("ROLLBACK")
conn.commit()

P.S. Documentation goes here https://www.postgresql.org/docs/9.4/static/sql-rollback.html

回答 4

以我的经验，这些错误是通过以下方式发生的：

try:
    code_that_executes_bad_query()
    # transaction on DB is now bad
except:
    pass

# transaction on db is still bad
code_that_executes_working_query() # raises transaction error

第二个查询没有任何问题，但是由于发现了真正的错误，因此第二个查询引发了错误（信息少得多）。

编辑：仅当except子句捕获IntegrityError（或任何其他低级数据库异常）时，才会发生这种情况；如果捕获到DoesNotExist此类错误，则不会出现此错误，因为DoesNotExist这不会破坏事务。

这里的类是不要尝试/exceptions/通过。

点击查看英文原文

In my experience, these errors happen this way:

try:
    code_that_executes_bad_query()
    # transaction on DB is now bad
except:
    pass

# transaction on db is still bad
code_that_executes_working_query() # raises transaction error

There nothing wrong with the second query, but since the real error was caught, the second query is the one that raises the (much less informative) error.

edit: this only happens if the except clause catches IntegrityError (or any other low level database exception), If you catch something like DoesNotExist this error will not come up, because DoesNotExist does not corrupt the transaction.

The lesson here is don’t do try/except/pass.

回答 5

我认为使用PostgreSQL时，priestc提到的模式更可能是此问题的常见原因。

但是，我认为该模式有有效的用途，我不认为这个问题应该成为始终避免它的原因。例如：

try:
    profile = user.get_profile()
except ObjectDoesNotExist:
    profile = make_default_profile_for_user(user)

do_something_with_profile(profile)

如果您确实对这种模式感到满意，但是想避免到处都是显式的事务处理代码，那么您可能希望考虑启用自动提交模式（PostgreSQL 8.2+）：https ://docs.djangoproject.com/en/ dev / ref / databases /＃autocommit-mode

DATABASES['default'] = {
    #.. you usual options...
    'OPTIONS': {
        'autocommit': True,
    }
}

我不确定是否有重要的性能考虑因素（或任何其他类型的考虑因素）。

点击查看英文原文

I think the pattern priestc mentions is more likely to be the usual cause of this issue when using PostgreSQL.

However I feel there are valid uses for the pattern and I don’t think this issue should be a reason to always avoid it. For example:

try:
    profile = user.get_profile()
except ObjectDoesNotExist:
    profile = make_default_profile_for_user(user)

do_something_with_profile(profile)

If you do feel OK with this pattern, but want to avoid explicit transaction handling code all over the place then you might want to look into turning on autocommit mode (PostgreSQL 8.2+): https://docs.djangoproject.com/en/dev/ref/databases/#autocommit-mode

DATABASES['default'] = {
    #.. you usual options...
    'OPTIONS': {
        'autocommit': True,
    }
}

I am unsure if there are important performance considerations (or of any other type).

回答 6

如果您在交互式外壳程序中得到此消息并需要快速修复，请执行以下操作：

from django.db import connection
connection._rollback()

最初在此答案中看到

点击查看英文原文

If you get this while in interactive shell and need a quick fix, do this:

from django.db import connection
connection._rollback()

originally seen in this answer

回答 7

在postgres终端上运行有故障的事务时，我遇到了类似的行为。此后什么都没有发生，因为database处于的状态error。但是，如果可以避免的话，只是快速解决方法rollback transaction。以下对我有用：

COMMIT;

点击查看英文原文

I encountered a similar behavior while running a malfunctioned transaction on the postgres terminal. Nothing went through after this, as the database is in a state of error. However, just as a quick fix, if you can afford to avoid rollback transaction. Following did the trick for me:

COMMIT;

回答 8

我有silimar问题。解决的办法是迁移数据库（manage.py syncdb或者manage.py schemamigration --auto <table name>如果您使用南方）。

点击查看英文原文

I’ve got the silimar problem. The solution was to migrate db (manage.py syncdb or manage.py schemamigration --auto <table name> if you use south).

回答 9

只是使用回滚

范例程式码

try:
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")
except:
    cur.execute("rollback")
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")
点击查看英文原文

just use rollback

Example code

try:
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")
except:
    cur.execute("rollback")
    cur.execute("CREATE TABLE IF NOT EXISTS test2 (id serial, qa text);")

回答 10

我也刚遇到这个错误，但是它掩盖了另一个更相关的错误消息，其中代码试图将125个字符的字符串存储在100个字符的列中：

DatabaseError: value too long for type character varying(100)

我必须调试代码才能显示以上消息，否则它将显示

DatabaseError: current transaction is aborted
点击查看英文原文

I just had this error too but it was masking another more relevant error message where the code was trying to store a 125 characters string in a 100 characters column:

DatabaseError: value too long for type character varying(100)

I had to debug through the code for the above message to show up, otherwise it displays

DatabaseError: current transaction is aborted

回答 11

作为对@priestc和@Sebastian的回应，如果您做这样的事情怎么办？

try:
    conn.commit()
except:
    pass

cursor.execute( sql )
try: 
    return cursor.fetchall()
except: 
    conn.commit()
    return None

我只是尝试了这段代码，它似乎可以正常工作，无需关心任何可能的错误就可以静默失败，并且在查询良好时可以正常工作。

点击查看英文原文

In response to @priestc and @Sebastian, what if you do something like this?

try:
    conn.commit()
except:
    pass

cursor.execute( sql )
try: 
    return cursor.fetchall()
except: 
    conn.commit()
    return None

I just tried this code and it seems to work, failing silently without having to care about any possible errors, and working when the query is good.

回答 12

我相信@AnujGupta的答案是正确的。但是，回滚本身会引发异常，您应该捕获并处理该异常：

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    try:
        transaction.rollback()
    except transaction.TransactionManagementError:
        # Log or handle otherwise

如果发现要在各个save()位置重写此代码，则可以提取方法：

import traceback
def try_rolling_back():
    try:
        transaction.rollback()
        log.warning('rolled back')  # example handling
    except transaction.TransactionManagementError:
        log.exception(traceback.format_exc())  # example handling

最后，您可以使用装饰器装饰它，该装饰器保护使用的方法save()：

from functools import wraps
def try_rolling_back_on_exception(fn):
    @wraps(fn)
    def wrapped(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except:
            traceback.print_exc()
            try_rolling_back()
    return wrapped

@try_rolling_back_on_exception
def some_saving_method():
    # ...
    model.save()
    # ...

即使您实现了上面的装饰器，try_rolling_back()在需要进行特殊处理而通用装饰器处理还不够的情况下，如果需要手动使用提取方法，将其保留为提取方法仍然很方便。

点击查看英文原文

I believe @AnujGupta’s answer is correct. However the rollback can itself raise an exception which you should catch and handle:

from django.db import transaction, DatabaseError
try:
    a.save()
except DatabaseError:
    try:
        transaction.rollback()
    except transaction.TransactionManagementError:
        # Log or handle otherwise

If you find you’re rewriting this code in various save() locations, you can extract-method:

import traceback
def try_rolling_back():
    try:
        transaction.rollback()
        log.warning('rolled back')  # example handling
    except transaction.TransactionManagementError:
        log.exception(traceback.format_exc())  # example handling

Finally, you can prettify it using a decorator that protects methods which use save():

from functools import wraps
def try_rolling_back_on_exception(fn):
    @wraps(fn)
    def wrapped(*args, **kwargs):
        try:
            return fn(*args, **kwargs)
        except:
            traceback.print_exc()
            try_rolling_back()
    return wrapped

@try_rolling_back_on_exception
def some_saving_method():
    # ...
    model.save()
    # ...

Even if you implement the decorator above, it’s still convenient to keep try_rolling_back() as an extracted method in case you need to use it manually for cases where specific handling is required, and the generic decorator handling isn’t enough.

回答 13

这对我来说是非常奇怪的行为。我很惊讶没有人想到保存点。在我的代码中，查询失败是预期的行为：

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
    return skipped

我已经以这种方式更改代码以使用保存点：

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    sid = transaction.savepoint()
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
            transaction.savepoint_rollback(sid)
        else:
            transaction.savepoint_commit(sid)
    return skipped
点击查看英文原文

This is very strange behavior for me. I’m surprised that no one thought of savepoints. In my code failing query was expected behavior:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
    return skipped

I have changed code this way to use savepoints:

from django.db import transaction
@transaction.commit_on_success
def update():
    skipped = 0
    sid = transaction.savepoint()
    for old_model in OldModel.objects.all():
        try:
            Model.objects.create(
                group_id=old_model.group_uuid,
                file_id=old_model.file_uuid,
            )
        except IntegrityError:
            skipped += 1
            transaction.savepoint_rollback(sid)
        else:
            transaction.savepoint_commit(sid)
    return skipped

回答 14

在Flask Shell中，我需要做的只是session.rollback()克服这个问题。

点击查看英文原文

In Flask shell, all I needed to do was a session.rollback() to get past this.

回答 15

我已经遇到了这个问题，由于错误交易尚未正确结束，因此出现错误，我在这里找到了postgresql_transactionsTransaction Control命令

交易控制

以下命令用于控制交易 

BEGIN TRANSACTION − To start a transaction.

COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

ROLLBACK − To rollback the changes.

所以我使用END TRANSACTION来结束错误TRANSACTION，如下代码：

    for key_of_attribute, command in sql_command.items():
        cursor = connection.cursor()
        g_logger.info("execute command :%s" % (command))
        try:
            cursor.execute(command)
            rows = cursor.fetchall()
            g_logger.info("the command:%s result is :%s" % (command, rows))
            result_list[key_of_attribute] = rows
            g_logger.info("result_list is :%s" % (result_list))
        except Exception as e:
            cursor.execute('END TRANSACTION;')
            g_logger.info("error command :%s and error is :%s" % (command, e))
    return result_list
点击查看英文原文

I have met this issue , the error comes out since the error transactions hasn’t been ended rightly, I found the postgresql_transactions of Transaction Control command here

Transaction Control

The following commands are used to control transactions 

BEGIN TRANSACTION − To start a transaction.

COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

ROLLBACK − To rollback the changes.

so i use the END TRANSACTION to end the error TRANSACTION, code like this:

    for key_of_attribute, command in sql_command.items():
        cursor = connection.cursor()
        g_logger.info("execute command :%s" % (command))
        try:
            cursor.execute(command)
            rows = cursor.fetchall()
            g_logger.info("the command:%s result is :%s" % (command, rows))
            result_list[key_of_attribute] = rows
            g_logger.info("result_list is :%s" % (result_list))
        except Exception as e:
            cursor.execute('END TRANSACTION;')
            g_logger.info("error command :%s and error is :%s" % (command, e))
    return result_list

回答 16

您可以通过“ set_isolation_level（0）”禁用交易

点击查看英文原文

you could disable transaction via “set_isolation_level(0)”

知识问答

如何在Python上使用“ pip”安装psycopg2？

问题：如何在Python上使用“ pip”安装psycopg2？

我在用着 virtualenv，我需要安装“ psycopg2”。

我已经完成以下工作：

pip install http://pypi.python.org/packages/source/p/psycopg2/psycopg2-2.4.tar.gz#md5=24f4368e2cfdc1a2b03282ddda814160

我有以下消息：

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 'setup.cfg'.
    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 'pip-egg-info\psycopg2.egg-info\SOURCES.txt'

warning: manifest_maker: standard file '-c' 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 'setup.cfg'.

----------------------------------------
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

我的问题是，我只需要这样做才能使psycopg2工作？

python setup.py build_ext --pg-config /path/to/pg_config build ...
点击查看英文原文

I’m using virtualenv and I need to install “psycopg2”.

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 'setup.cfg'.
    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 'pip-egg-info\psycopg2.egg-info\SOURCES.txt'

warning: manifest_maker: standard file '-c' 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 'setup.cfg'.

----------------------------------------
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 ...

回答 0

注意：早在不久之前，PyPI中就存在Windows的二进制轮子，因此Windows用户不再是问题。以下是适用于Linux和Mac用户的解决方案，因为他们中的很多人都是通过网络搜索找到这篇文章的。

选项1

psycopg2-binary而是安装PyPI软件包，它具有适用于Linux和Mac OS的Python轮子。

pip install psycopg2-binary

选项2

安装先决条件来构建 psycopg2从源代码软件包：

Debian / Ubuntu

Python 3

sudo apt install libpq-dev python3-dev

您可能需要安装 python3.8-dev或类似的工具，例如Python 3.8。

Python 2 1

sudo apt install libpq-dev python-dev

如果还不够，请尝试

sudo apt install build-essential

要么

sudo apt install postgresql-server-dev-all

在再次安装psycopg2之前。

CentOS的6

参见班杰的答案

1真的吗？2020年

点击查看英文原文

Note: Since a while back, there are binary wheels for Windows in PyPI, so this should no longer be an issue for Windows users. Below are solutions for Linux, Mac users, since lots of them find this post through web searches.

Option 1

Install the psycopg2-binary PyPI package instead, it has Python wheels for Linux and Mac OS.

pip install psycopg2-binary

Option 2

Install the prerequsisites for building the psycopg2 package from source:

Debian/Ubuntu

Python 3

sudo apt install libpq-dev python3-dev

You might need to install python3.8-dev or similar for e.g. Python 3.8.

Python 21

sudo apt install libpq-dev python-dev

If that’s not enough, try

sudo apt install build-essential

or

sudo apt install postgresql-server-dev-all

as well before installing psycopg2 again.

CentOS 6

See Banjer’s answer

1 Really? It’s 2020

回答 1

在CentOS上，您需要postgres开发软件包：

sudo yum install python-devel postgresql-devel

至少这是CentOS 6上的解决方案。

点击查看英文原文

On CentOS, you need the postgres dev packages:

sudo yum install python-devel postgresql-devel

That was the solution on CentOS 6 at least.

回答 2

在安装了Postgres.app 9.3.2.0 RC2的Mac Mavericks上，安装Postgres之后，我需要使用以下代码：

sudo PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin pip install psycopg2

点击查看英文原文

On Mac Mavericks with Postgres.app version 9.3.2.0 RC2 I needed to use the following code after installing Postgres:

sudo PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin pip install psycopg2

回答 3

如果您使用的是Mac，则可以使用自制软件

brew install postgresql

其他所有选项都在这里：http : //www.postgresql.org/download/macosx/

祝好运

点击查看英文原文

if you’re on a mac you can use homebrew

brew install postgresql

And all other options are here: http://www.postgresql.org/download/macosx/

Good luck

回答 4

我最近在Windows机器上配置了psycopg2。最简单的安装是使用Windows可执行二进制文件。您可以在http://stickpeople.com/projects/python/win-psycopg/中找到它。

要在虚拟环境中安装本机二进制文件，请使用easy_install： 

C:\virtualenv\Scripts\> activate.bat
(virtualenv) C:\virtualenv\Scripts\> easy_install psycopg2-2.5.win32-py2.7-pg9.2.4-release.exe
点击查看英文原文

I recently configured psycopg2 on a windows machine. The easiest install is using a windows executable binary. You can find it at http://stickpeople.com/projects/python/win-psycopg/.

To install the native binary in a virtual envrionment, use easy_install: 

C:\virtualenv\Scripts\> activate.bat
(virtualenv) C:\virtualenv\Scripts\> easy_install psycopg2-2.5.win32-py2.7-pg9.2.4-release.exe

回答 5

对于Python 3，您应该sudo apt-get install libpq-dev python3-dev在Debian下使用。

点击查看英文原文

For Python 3 you should use sudo apt-get install libpq-dev python3-dev under Debian.

回答 6

这对我有用（在RHEL，CentOS上：

sudo yum install postgresql postgresql-devel python-devel

现在在pip install中包含指向您的postgresql二进制目录的路径：

sudo PATH=$PATH:/usr/pgsql-9.3/bin/ pip install psycopg2

确保包括正确的路径。就这样 ：）

更新：对于python 3，请安装python3-devel而不是python-devel

点击查看英文原文

This is what worked for me (On RHEL, CentOS:

sudo yum install postgresql postgresql-devel python-devel

And now include the path to your postgresql binary dir with you pip install:

sudo PATH=$PATH:/usr/pgsql-9.3/bin/ pip install psycopg2

Make sure to include the correct path. Thats all :)

UPDATE: For python 3, please install python3-devel instead of python-devel

回答 7

如果使用Mac OS，则应从源代码安装PostgreSQL。安装完成后，您需要使用以下方法添加此路径：

export PATH=/local/pgsql/bin:$PATH

或者您可以像这样添加路径：

export PATH=.../:usr/local/pgsql/bin

在您的.profile文件或.zshrc文件中。

这可能因操作系统而异。

您可以从http://www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source/遵循安装过程

点击查看英文原文

If you using Mac OS, you should install PostgreSQL from source. After installation is finished, you need to add this path using:

export PATH=/local/pgsql/bin:$PATH

or you can append the path like this:

export PATH=.../:usr/local/pgsql/bin

in your .profile file or .zshrc file.

This maybe vary by operating system.

You can follow the installation process from http://www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source/

回答 8

到目前为止的答案太像魔术食谱。收到的错误告诉您pip无法找到PostgreSQL查询库的所需部分。可能是因为您在操作系统的非标准位置安装了它，这就是为什么该消息建议使用–pg-config选项的原因。

但是更常见的原因是您根本没有安装libpq。这通常发生在没有安装PostgreSQL服务器的机器上，因为您只想运行客户端应用程序，而不是服务器本身。每个OS /发行版都不相同，例如在Debian / Ubuntu上，您需要安装libpq-dev。这使您可以针对PostgreSQL查询库编译和链接代码。

大多数答案还建议安装Python开发库。小心。如果仅使用发行版中安装的默认Python，则可以使用，但是如果您使用的是较新版本，则可能会引起问题。如果您在此计算机上构建了Python，那么您已经具有编译C / C ++库与Python交互所需的dev库。只要您使用的是正确的pip版本，即与python二进制文件安装在同一bin文件夹中的版本，您便都已准备就绪。无需安装旧版本。

点击查看英文原文

The answers so far are too much like magic recipes. The error that you received tells you that pip cannot find a needed part of the PostgreSQL Query library. Possibly this is because you have it installed in a non-standard place for your OS which is why the message suggests using the –pg-config option.

But a more common reason is that you don’t have libpq installed at all. This commonly happens on machines where you do NOT have PostgreSQL server installed because you only want to run client apps, not the server itself. Each OS/distro is different, for instance on Debian/Ubuntu you need to install libpq-dev. This allows you to compile and link code against the PostgreSQL Query library.

Most of the answers also suggest installing a Python dev library. Be careful. If you are only using the default Python installed by your distro, that will work, but if you have a newer version, it could cause problems. If you have built Python on this machine then you already have the dev libraries needed for compiling C/C++ libraries to interface with Python. As long as you are using the correct pip version, the one installed in the same bin folder as the python binary, then you are all set. No need to install the old version.

回答 9

上 Debian/Ubuntu：

首先安装和构建psycopg2软件包的依赖项：

# apt-get build-dep python-psycopg2

然后在您的虚拟环境中，编译并安装psycopg2模块：

(env)$ pip install psycopg2
点击查看英文原文

On Debian/Ubuntu:

First install and build dependencies of psycopg2 package:

# apt-get build-dep python-psycopg2

Then in your virtual environment, compile and install psycopg2 module:

(env)$ pip install psycopg2

回答 10

我已经在Windows中首先安装到基本python安装中的位置之前完成了此操作。

然后，您手动将已安装的psycopg2复制到virtualenv安装中。

它不漂亮，但是可以用。

点击查看英文原文

I’ve done this before where in windows you install first into your base python installation.

Then, you manually copy the installed psycopg2 to the virtualenv install.

It’s not pretty, but it works.

回答 11

除了安装必需的软件包外，我还需要手动将PostgreSQL bin目录添加到PATH。在之前
$vi ~/.bash_profile
添加。PATH=/usr/pgsql-9.2/bin:$PATHexport PATH
$source ~/.bash_profile
$pip install psycopg2

点击查看英文原文

Besides installing the required packages, I also needed to manually add PostgreSQL bin directory to PATH.
$vi ~/.bash_profile
Add PATH=/usr/pgsql-9.2/bin:$PATH before export PATH.
$source ~/.bash_profile
$pip install psycopg2

回答 12

在Windows XP上，如果未安装postgres，则会出现此错误…

点击查看英文原文

On windows XP you get this error if postgres is not installed …

回答 13

我使用PG下载网站http://www.postgresql.org/download/linux/redhat/上的RedHat / CentOS存储库安装了Postgresql92 。

要获取pg_config，我必须将/usr/pgsql-9.2/bin添加到PATH。

点击查看英文原文

I installed Postgresql92 using the RedHat / CentOS repository on PG’s downloads site http://www.postgresql.org/download/linux/redhat/

To get pg_config, I had to add /usr/pgsql-9.2/bin to PATH.

回答 14

在安装psycopg2之前，您需要安装python-dev软件包。

如果您使用的是Linux（可能还有其他系统，但我不能从经验上讲），则在安装dev软件包时，需要确保准确地了解运行的python版本。

例如，当我使用命令时：

sudo apt-get install python3-dev

尝试执行以下操作时，我仍然遇到相同的错误 

pip install psycopg2

当我使用python 3.7时，我需要使用命令

sudo apt-get install python3.7-dev

完成此操作后，我再也不会遇到任何问题。显然，如果您使用的是python 3.5版，则可以将该7更改为5。

点击查看英文原文

Before you can install psycopg2 you will need to install the python-dev package.

If you’re working from Linux (and possibly other systems but i can’t speak from experience) you will need to make sure to be quite exact about what version of python your running when installing the dev package.

For example when I used the command:

sudo apt-get install python3-dev

I still ran into the same error when trying to 

pip install psycopg2

As I am using python 3.7 I needed to use the command

sudo apt-get install python3.7-dev

Once I did this I ran into no more issues. Obviously if your on python version 3.5 you would change that 7 to a 5.

回答 15

我已经为此奋斗了好几天，终于找到了如何使“ pip install psycopg2”命令在Windows（运行Cygwin）的virtualenv中运行的方法。

我碰到了“找不到pg_config可执行文件”。错误，但我已经在Windows中下载并安装了postgres。它也安装在Cygwin中。在Cygwin中运行“哪个pg_config”给出了“ / usr / bin / pg_config”，而运行“ pg_config”给出了合理的输出-但是，与Cygwin一起安装的版本是：

版本= PostgreSQL 8.2.11

这不适用于当前版本的psycopg2，后者似乎至少需要9.1。当我在Windows路径中添加“ c：\ Program Files \ PostgreSQL \ 9.2 \ bin”时，Cygwin pip安装程序能够找到正确的PostgreSQL版本，并且能够使用pip成功安装该模块。（无论如何，这可能比使用Cygwin版本的PostgreSQL更可取，因为本机版本运行得更快）。

点击查看英文原文

I’ve been battling with this for days, and have finally figured out how to get the “pip install psycopg2” command to run in a virtualenv in Windows (running Cygwin).

I was hitting the “pg_config executable not found.” error, but I had already downloaded and installed postgres in Windows. It installed in Cygwin as well; running “which pg_config” in Cygwin gave “/usr/bin/pg_config”, and running “pg_config” gave sane output — however the version installed with Cygwin is:

VERSION = PostgreSQL 8.2.11

This won’t work with the current version of psycopg2, which appears to require at least 9.1. When I added “c:\Program Files\PostgreSQL\9.2\bin” to my Windows path, the Cygwin pip installer was able to find the correct version of PostgreSQL, and I was able to successfully install the module using pip. (This is probably preferable to using the Cygwin version of PostgreSQL anyway, as the native version will run much quicker).

回答 16

在Fedora 24上：对于Python 3.x

sudo dnf install postgresql-devel python3-devel

sudo dnf install redhat-rpm-config

激活您的虚拟环境：

pip install psycopg2
点击查看英文原文

On Fedora 24: For Python 3.x

sudo dnf install postgresql-devel python3-devel

sudo dnf install redhat-rpm-config

Activate your Virtual Environment:

pip install psycopg2

回答 17

Psycopg2取决于Postgres库。在Ubuntu上，您可以使用：

apt-get install libpq-dev

然后：

pip install psycopg2
点击查看英文原文

Psycopg2 Depends on Postgres Libraries. On Ubuntu You can use:

apt-get install libpq-dev

Then:

pip install psycopg2

回答 18

对于Windows较低的用户，他们不得不从下面的链接安装psycopg2，只需将其安装到您设置的任何Python安装中即可。它将名为“ psycopg2”的文件夹放置在python安装的site-packages文件夹中。

之后，只需将该文件夹复制到virtualenv的site-packages目录中，就不会有问题。

这是您可以找到安装psycopg2的可执行文件的链接

http://www.lfd.uci.edu/~gohlke/pythonlibs/

点击查看英文原文

For lowly Windows users were stuck having to install psycopg2 from the link below, just install it to whatever Python installation you have setup. It will place the folder named “psycopg2” in the site-packages folder of your python installation.

After that, just copy that folder to the site-packages directory of your virtualenv and you will have no problems.

here is the link you can find the executable to install psycopg2

http://www.lfd.uci.edu/~gohlke/pythonlibs/

回答 19

在OpenSUSE 13.2上，此操作已修复：

sudo zypper in postgresql-devel
点击查看英文原文

On OpenSUSE 13.2, this fixed it:

sudo zypper in postgresql-devel

回答 20

我可以将其安装在Windows计算机上，并通过以下命令将Anaconda / Spyder与python 2.7结合使用：

 !pip install psycopg2

然后建立与数据库的连接：

 import psycopg2
 conn = psycopg2.connect(dbname='dbname',host='host_name',port='port_number', user='user_name', password='password')
点击查看英文原文

I could install it in a windows machine and using Anaconda/Spyder with python 2.7 through the following commands:

 !pip install psycopg2

Then to establish the connection to the database:

 import psycopg2
 conn = psycopg2.connect(dbname='dbname',host='host_name',port='port_number', user='user_name', password='password')

回答 21

在Arch基本发行版中：

sudo pacman -S python-psycopg2
pip2 install psycopg2  # Use pip or pip3 to python3
点击查看英文原文

In Arch base distributions:

sudo pacman -S python-psycopg2
pip2 install psycopg2  # Use pip or pip3 to python3

回答 22

如果pip不起作用，则可以从此处https://pypi.python.org/pypi/psycopg2下载.whl文件 。python setup.py install

点击查看英文原文

if pip is not working than you can download .whl file from here https://pypi.python.org/pypi/psycopg2 extract it.. than python setup.py install

回答 23

在Ubuntu上，我只需要postgres dev软件包：

sudo apt-get install postgresql-server-dev-all

*在virtualenv中测试

点击查看英文原文

On Ubuntu I just needed the postgres dev package:

sudo apt-get install postgresql-server-dev-all

*Tested in a virtualenv

回答 24

在OSX 10.11.6（El Capitan）上

brew install postgresql
PATH=$PATH:/Library/PostgreSQL/9.4/bin pip install psycopg2
点击查看英文原文

On OSX 10.11.6 (El Capitan)

brew install postgresql
PATH=$PATH:/Library/PostgreSQL/9.4/bin pip install psycopg2

回答 25

在具有Macports的OSX上：

sudo port install postgresql96
export PATH=/opt/local/lib/postgresql96/bin:$PATH
点击查看英文原文

On OSX with macports:

sudo port install postgresql96
export PATH=/opt/local/lib/postgresql96/bin:$PATH

回答 26

我遇到了这个问题，主要原因是安装了2个相同的版本。一种通过postgres.app，另一种通过HomeBrew。

如果您选择仅保留APP：

brew unlink postgresql
pip3 install psycopg2
点击查看英文原文

I was having this problem, the main reason was with 2 equal versions installed. One by postgres.app and one by HomeBrew.

If you choose to keep only the APP:

brew unlink postgresql
pip3 install psycopg2

回答 27

在macOS Mojave上，请确保您使用适用于我的Command Line Tools 10.3进行了最新更新-通过Software Update对其进行了更新，Mojave上的Command Line Tools的先前版本对我不起作用。

点击查看英文原文

On macOS Mojave make sure you on newest update with Command Line Tools 10.3 – that worked for me – updated it with Software Update, previous version of Command Line Tools on Mojave did not work for me.

回答 28

试试这个Gentoo：

emerge dev-libs/libpqxx
点击查看英文原文

Try this in Gentoo:

emerge dev-libs/libpqxx

回答 29

在Windows上是这样工作
的在虚拟环境中通过pip安装flask之后，在命令提示符下运行此命令

>pip install psycopg2

检查一下

点击查看英文原文

On windows this is how it works
On the command prompt after installing flask via pip in virtual environment, run this command

>pip install psycopg2

Check this

知识问答

找不到pg_config可执行文件

问题：找不到pg_config可执行文件

我在安装psycopg2时遇到问题。尝试执行以下操作时出现以下错误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 'setup.cfg'.

----------------------------------------
Command python setup.py egg_info failed with error code 1 in /tmp/pip-build/psycopg2

但是问题出pg_config在我身上PATH; 它运行没有任何问题：

$ which pg_config
/usr/pgsql-9.1/bin/pg_config

我尝试将pg_config路径添加到setup.cfg文件中，并使用从其网站（http://initd.org/psycopg/）下载的源文件进行构建，然后收到以下错误消息！

Error: Unable to find 'pg_config' file in '/usr/pgsql-9.1/bin/'

但是实际上是那里！！！

这些错误使我感到困惑。有人可以帮忙吗？

顺便说一下，我sudo所有的命令。我也在使用RHEL 5.5。

点击查看英文原文

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 'setup.cfg'.

----------------------------------------
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 'pg_config' file in '/usr/pgsql-9.1/bin/'

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.

回答 0

pg_config位于postgresql-devel（libpq-dev在libpq-develCentos / Cygwin / Babun的Debian / Ubuntu中。）

点击查看英文原文

pg_config is in postgresql-devel (libpq-dev in Debian/Ubuntu, libpq-devel on Centos/Cygwin/Babun.)

回答 1

在Mac OS X上，我使用自制软件包管理器解决了该问题

brew install postgresql
点击查看英文原文

On Mac OS X, I solved it using the homebrew package manager

brew install postgresql

回答 2

你安装好了python-dev吗？如果已经拥有，请尝试安装libpq-dev

sudo apt-get install libpq-dev python-dev

从文章：如何在virtualenv下安装psycopg2

点击查看英文原文

Have you installed python-dev? If you already have, try also installing libpq-dev

sudo apt-get install libpq-dev python-dev

From the article: How to install psycopg2 under virtualenv

回答 3

同样在OSX上。从http://postgresapp.com/安装了Postgress.app，但存在相同的问题。

我pg_config在该应用程序的内容中找到了目录，并将目录添加到$PATH。

到了/Applications/Postgres.app/Contents/Versions/latest/bin。所以这个工作：export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"。

点击查看英文原文

Also on OSX. Installed Postgress.app from http://postgresapp.com/ but had the same issue.

I found pg_config in that app’s contents and added the dir to $PATH.

It was at /Applications/Postgres.app/Contents/Versions/latest/bin. So this worked: export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH".

回答 4

在高山上，包含的库pg_config为postgresql-dev。要安装，请运行：

apk add postgresql-dev
点击查看英文原文

On alpine, the library containing pg_config is postgresql-dev. To install, run:

apk add postgresql-dev

回答 5

这是在CentOS上首次安装对我有用的东西：

sudo yum install postgresql postgresql-devel python-devel

在Ubuntu上，只需使用等效的apt-get软件包。

sudo apt-get install postgresql postgresql-dev python-dev

现在在pip安装中包含postgresql二进制目录的路径，这对于基于Debain或RHEL的Linux应该都适用：

sudo PATH=$PATH:/usr/pgsql-9.3/bin/ pip install psycopg2

确保包括正确的路径。就这样 ：）

点击查看英文原文

This is what worked for me on CentOS, first install:

sudo yum install postgresql postgresql-devel python-devel

On Ubuntu just use the equivilent apt-get packages.

sudo apt-get install postgresql postgresql-dev python-dev

And now include the path to your postgresql binary dir with you pip install, this should work for either Debain or RHEL based Linux:

sudo PATH=$PATH:/usr/pgsql-9.3/bin/ pip install psycopg2

Make sure to include the correct path. Thats all :)

回答 6

apt-get build-dep python-psycopg2
点击查看英文原文 
apt-get build-dep python-psycopg2

回答 7

您应该添加在Ubuntu上的Postgres中使用的python要求。跑：

sudo apt-get install libpq-dev python-dev
点击查看英文原文

You should add python requirements used in Postgres on Ubuntu. Run:

sudo apt-get install libpq-dev python-dev

回答 8

综上所述，我也面临着完全相同的问题。在阅读了很多stackoverflow帖子和在线博客之后，对我有用的最终解决方案是：

1）在安装psycopg2之前，应先安装PostgreSQL（开发版或任何稳定版本）。

2）在安装psycopg2之前，必须显式设置pg_config文件（该文件通常位于PostgreSQL安装文件夹的bin文件夹中）。就我而言，PostgreSQL的安装路径为：

/opt/local/lib/postgresql91/

因此，为了显式设置pg_config文件的PATH，我在终端中输入了以下命令：

PATH=$PATH:/opt/local/lib/postgresql91/bin/

此命令可确保当您尝试通过pip安装psycopg2时，它将自动找到pg_config的路径。

我还在我的博客上发布了有关trace及其解决方案的完整错误，您可能希望参考。它适用于Mac OS X，但是pg_config PATH问题是通用的，也适用于Linux。

点击查看英文原文

Just to sum up, I also faced exactly same problem. After reading a lot of stackoverflow posts and online blogs, the final solution which worked for me is this:

1) PostgreSQL(development or any stable version) should be installed before installing psycopg2.

2) The pg_config file (this file normally resides in the bin folder of the PostgreSQL installation folder) PATH had to be explicitly setup before installing psycopg2. In my case, the installation PATH for PostgreSQL is:

/opt/local/lib/postgresql91/

so in order to explicitly set the PATH of pg_config file, I entered following command in my terminal:

PATH=$PATH:/opt/local/lib/postgresql91/bin/

This command ensures that when you try to pip install psycopg2, it would find the PATH to pg_config automatically this time.

I have also posted a full error with trace and its solution on my blog which you may want to refer. Its for Mac OS X but the pg_config PATH problem is generic and applicable to Linux also.

回答 9

sudo apt-get install libpq-dev 在Ubuntu 15.4上为我工作

点击查看英文原文

sudo apt-get install libpq-dev works for me on Ubuntu 15.4

回答 10

在Linux上Mint sudo apt-get install libpq-dev为我工作。

点击查看英文原文

On Linux Mint sudo apt-get install libpq-dev worked for me.

回答 11

您可以使用pip或在任何平台上安装预编译的二进制文件conda：

python -m pip install psycopg2-binary

要么

conda install psycopg2

请注意，psycopg2-binary pypi页面建议在生产中从源代码构建：

二进制软件包是开发和测试的实际选择，但在生产中，建议使用从源构建的软件包

要使用从源构建的软件包，请使用python -m pip install psycopg2。该过程将需要几个依赖项（文档）（重点是我的）：

  • 交流编译器。
  • 在Python的头文件。它们通常安装在python-dev之类的软件包中。诸如错误消息：Python.h：没有这样的文件或目录表示缺少Python标头。
  • 该libpq的头文件。它们通常安装在libpq-dev之类的软件包中。如果出现错误：libpq-fe.h：没有此类文件或目录，您将丢失它们。
  • 该pg_config程序：它通常是由安装的libpq-dev的包，但有时它是不是在路径目录。将它放在PATH中可以大大简化安装，因此请尝试运行pg_config –version：如果返回错误或意外的版本号，请找到包含正确libpq版本附带的pg_config的目录（通常是/ usr / lib / postgresql / XY / bin /）并将其添加到PATH： $ export PATH=/usr/lib/postgresql/X.Y/bin/:$PATH 您仅需要pg_config来编译psycopg2，而无需常规使用。
点击查看英文原文

You can install pre-compiled binaries on any platform with pip or conda:

python -m pip install psycopg2-binary

or

conda install psycopg2

Please be advised that the psycopg2-binary pypi page recommends building from source in production:

The binary package is a practical choice for development and testing but in production it is advised to use the package built from sources

To use the package built from sources, use python -m pip install psycopg2. That process will require several dependencies (documentation) (emphasis mine):

  • A C compiler.
  • The Python header files. They are usually installed in a package such as python-dev. A message such as error: Python.h: No such file or directory is an indication that the Python headers are missing.
  • The libpq header files. They are usually installed in a package such as libpq-dev. If you get an error: libpq-fe.h: No such file or directory you are missing them.
  • The pg_config program: it is usually installed by the libpq-dev package but sometimes it is not in a PATH directory. Having it in the PATH greatly streamlines the installation, so try running pg_config –version: if it returns an error or an unexpected version number then locate the directory containing the pg_config shipped with the right libpq version (usually /usr/lib/postgresql/X.Y/bin/) and add it to the PATH: $ export PATH=/usr/lib/postgresql/X.Y/bin/:$PATH You only need pg_config to compile psycopg2, not for its regular usage.

回答 12

UPDATE /etc/yum.repos.d/CentOS-Base.repo、[base]和[updates]部分
ADD exclude = postgresql *

curl -O http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpmr  
rpm -ivh pgdg-centos91-9.1-4.noarch.rpm

yum install postgresql  
yum install postgresql-devel

PATH=$PATH:/usr/pgsql-9.1/bin/

pip install psycopg2
点击查看英文原文

UPDATE /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections
ADD exclude=postgresql*

curl -O http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpmr  
rpm -ivh pgdg-centos91-9.1-4.noarch.rpm

yum install postgresql  
yum install postgresql-devel

PATH=$PATH:/usr/pgsql-9.1/bin/

pip install psycopg2

回答 13

对于运行OS X的用户，此解决方案对我有用：

1）安装Postgres.app：

http://www.postgresql.org/download/macosx/

2）然后打开终端并运行以下命令，将其显示为{{version}}的位置替换为Postgres版本号：

导出PATH = $ PATH：/Applications/Postgres.app/Contents/Versions / {{version}} / bin

例如

导出PATH = $ PATH：/Applications/Postgres.app/Contents/Versions/9.4/bin

点击查看英文原文

For those running OS X, this solution worked for me:

1) Install Postgres.app:

http://www.postgresql.org/download/macosx/

2) Then open the Terminal and run this command, replacing where it says {{version}} with the Postgres version number:

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/{{version}}/bin

e.g.

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin

回答 14

尝试将其添加到PATH：

PATH=$PATH:/usr/pgsql-9.1/bin/ ./pip install psycopg2
点击查看英文原文

Try to add it to PATH:

PATH=$PATH:/usr/pgsql-9.1/bin/ ./pip install psycopg2

回答 15

Ali的解决方案对我有用，但是我在查找bin文件夹位置时遇到了麻烦。在Mac OS X上查找路径的快速方法是打开psql（顶部菜单栏中有一个快速链接）。这将打开一个单独的终端窗口，在第二行，您的Postgres安装路径将如下所示：

My-MacBook-Pro:~ Me$ /Applications/Postgres93.app/Contents/MacOS/bin/psql ; exit;

您的pg_config文件在该bin文件夹中。因此，在安装psycopg2之前，请设置pg_config文件的路径：

PATH=$PATH:/Applications/Postgres93.app/Contents/MacOS/bin/

或较新版本：

PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin

然后安装psycopg2。

点击查看英文原文

Ali’s solution worked for me but I was having trouble finding the bin folder location. A quick way to find the path on Mac OS X is to open psql (there’s a quick link in the top menu bar). This will open a separate terminal window and on the second line the path of your Postgres installation will appear like so:

My-MacBook-Pro:~ Me$ /Applications/Postgres93.app/Contents/MacOS/bin/psql ; exit;

Your pg_config file is in that bin folder. Therefore, before installing psycopg2 set the path of the pg_config file:

PATH=$PATH:/Applications/Postgres93.app/Contents/MacOS/bin/

or for newer version:

PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin

Then install psycopg2.

回答 16

在安装psycopg2之前，您需要升级您的pip。使用此命令

pip install --upgrade pip
点击查看英文原文

You need to upgrade your pip before installing psycopg2. Use this command

pip install --upgrade pip

回答 17

我将把这个留给下一个不幸的灵魂，尽管所有提供的解决方案都无法解决这个问题。只需使用sudo pip3 install psycopg2-binary

点击查看英文原文

I’m going to leave this here for the next unfortunate soul who can’t get around this problem despite all the provided solutions. Simply use sudo pip3 install psycopg2-binary

回答 18

刚刚通过以下方法解决了Cent OS 7中的问题：

export PATH=$PATH:/usr/pgsql-9.5/bin

确保您的PostgreSql版本与上面的正确版本匹配。

点击查看英文原文

Just solved the problem in Cent OS 7 by:

export PATH=$PATH:/usr/pgsql-9.5/bin

make sure your PostgreSql version matches the right version above.

回答 19

在Mac OS X上，如果您使用的是Postgres App（http://postgresapp.com/）：

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

无需在此命令中指定Postgres的版本。它将始终指向最新。

并做 

pip install psycopg2

PS：如果更改未反映出您可能需要重新启动终端/命令提示符

资源

点击查看英文原文

On Mac OS X and If you are using Postgres App (http://postgresapp.com/):

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

No need to specify version of Postgres in this command. It will be always pointed to latest.

and do 

pip install psycopg2

P.S: If Changes doesn’t reflect you may need to restart the Terminal/Command prompt

Source

回答 20

安装python-psycopg2在Arch Linux上为我解决了这个问题：

pacman -S python-psycopg2
点击查看英文原文

Installing python-psycopg2 solved it for me on Arch Linux:

pacman -S python-psycopg2

回答 21

在Windows上，您可能需要安装Psycopg的Windows端口，该端口在psycopg的文档中建议使用。

点击查看英文原文

On Windows, You may want to install the Windows port of Psycopg, which is recommended in psycopg’s documentation.

回答 22

在MacOS上，最简单的解决方案是将正确的二进制文件符号链接到Postgres软件包下。

sudo ln -s /Applications/Postgres.app/Contents/Versions/latest/bin/pg_config /usr/local/bin/pg_config

这是相当无害的，并且如果需要，所有应用程序都可以在系统范围内使用它。

点击查看英文原文

On MacOS, the simplest solution will be to symlink the correct binary, that is under the Postgres package.

sudo ln -s /Applications/Postgres.app/Contents/Versions/latest/bin/pg_config /usr/local/bin/pg_config

This is fairly harmless, and all the applications will be able to use it system wide, if required.

回答 23

sudo yum安装postgresql-devel（centos6X）

点安装psycopg2 == 2.5.2

点击查看英文原文

sudo yum install postgresql-devel (centos6X)

pip install psycopg2==2.5.2

回答 24

在这里，为了确保OS X的完整性：如果从MacPorts安装PostgreSQL，则pg_config将位于 /opt/local/lib/postgresql94/bin/pg_config。

安装MacPorts时，它已经添加了 /opt/local/bin到PATH中。

因此，这将解决问题： $ sudo ln -s /opt/local/lib/postgresql94/bin/pg_config /opt/local/bin/pg_config

现在pip install psycopg2将可以pg_config毫无问题地运行。

点击查看英文原文

Here, for OS X completeness: if you install PostgreSQL from MacPorts, pg_config will be in /opt/local/lib/postgresql94/bin/pg_config.

When you installed MacPorts, it already added /opt/local/bin to your PATH.

So, this will fix the problem: $ sudo ln -s /opt/local/lib/postgresql94/bin/pg_config /opt/local/bin/pg_config

Now pip install psycopg2 will be able to run pg_config without issues.

回答 25

对于使用zshshell的macOS Catalina 上也安装了postgres应用程序的用户：

打开~/.zshrc文件，并添加以下行：

export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"

然后关闭所有终端，重新打开它们，您将解决问题。

如果您不想关闭终端，只需输入要继续使用的终端即可source ~/.zshrc。

点击查看英文原文

To those on macOS Catalina using the zsh shell who have also installed the postgres app:

Open your ~/.zshrc file, and add the following line:

export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"

Then close all your terminals, reopen them, and you’ll have resolved your problem.

If you don’t want to close your terminals, simply enter source ~/.zshrc in whatever terminal you’d like to keep working on.

回答 26

对于mac用户，扩展您的path变量以包括PostgreSQL这样的export PATH=$PATH:/Library/PostgreSQL/12/bin。

点击查看英文原文

For mac users, extend your path variable to include PostgreSQL like this export PATH=$PATH:/Library/PostgreSQL/12/bin.

回答 27

这是我设法安装psycopg2的方法

$ wget http://initd.org/psycopg/tarballs/PSYCOPG-2-5/psycopg2-2.5.3.tar.gz
$ tar -xzf psycopg2-2.5.3.tar.gz
$ cd psycopg2-2.5.3
$ pip install .
点击查看英文原文

This is how I managed to install psycopg2

$ wget http://initd.org/psycopg/tarballs/PSYCOPG-2-5/psycopg2-2.5.3.tar.gz
$ tar -xzf psycopg2-2.5.3.tar.gz
$ cd psycopg2-2.5.3
$ pip install .

回答 28

我敢肯定，您会遇到与我相同的“问题”，因此，我将为您提供极为简单的解决方案…

在您的情况下，您需要添加到$ PATH（或作为命令参数）的实际路径是：

/usr/pgsql-9.1/bin/pg_config

不

/usr/pgsql-9.1/bin

例如，如果您随后运行python setup.py脚本，则可以这样运行它：

python setup.py build_ext --pg-config /usr/pgsql-9.1/bin/pg_config build

可能为时已晚，但仍然是最简单的解决方案。

之后编辑：

在进一步测试下，我发现如果您最初以以下形式将路径添加到pg_config 

/usr/pgsql-9.1/bin

（在../bin之后没有/ pg_config）并运行pip install命令，它将起作用。

但是，如果您随后决定按照说明运行python setup.py，则必须在….. / bin之后使用/ pg_config指定路径，即

python setup.py build_ext --pg-config /usr/pgsql-9.1/bin/pg_config build
点击查看英文原文

I am pretty sure you’ve experienced the same “problem” i did, therefore I’ll offer you the extremely easy solution…

In your case, the actual path that you need to add to $PATH (or as a command param) is:

/usr/pgsql-9.1/bin/pg_config

not

/usr/pgsql-9.1/bin

E.g. if you run the python setup.py script afterwards, you would run it like this:

python setup.py build_ext --pg-config /usr/pgsql-9.1/bin/pg_config build

Probably too late, but still the easiest solution.

LATER EDIT:

Under further test I found out that if you initially add the path to pg_config in the form 

/usr/pgsql-9.1/bin

(without /pg_config after …../bin) and run the pip install command it will work.

However, if you then decide to follow the indication to run python setup.py, you will have to specify the path with /pg_config after …../bin, i.e.

python setup.py build_ext --pg-config /usr/pgsql-9.1/bin/pg_config build

回答 29

对于CentOS / RedHat，请确保这/etc/alternatives/pgsql-pg_config是一个不间断的符号链接

点击查看英文原文

for CentOS/RedHat make sure that /etc/alternatives/pgsql-pg_config is a non-broken symlink