在 Python 中的 psycopg2 中执行 .sql 模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17261061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute .sql schema in psycopg2 in Python
提问by linkyndy
I have a PostgreSQL schema stored in .sql file. It looks something like:
我有一个存储在 .sql 文件中的 PostgreSQL 模式。它看起来像：
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
facebook_id TEXT NOT NULL,
name TEXT NOT NULL,
access_token TEXT,
created INTEGER NOT NULL
);
How shall I run this schema after connecting to the database?
连接到数据库后如何运行此模式？
My existing Python code works for SQLite databases:
我现有的 Python 代码适用于 SQLite 数据库：
# Create database connection
self.connection = sqlite3.connect("example.db")
# Run database schema
with self.connection as cursor:
cursor.executescript(open("schema.sql", "r").read())
But the psycopg2 doesn't have an executescriptmethod on the cursor. So, how can I achieve this?
但是 psycopg2executescript在光标上没有方法。那么，我怎样才能做到这一点？
采纳答案by Craig Ringer
You can just use execute:
你可以只使用execute：
with self.connection as cursor:
cursor.execute(open("schema.sql", "r").read())
though you may want to set psycopg2 to autocommitmode firstso you can use the script's own transaction management.
尽管您可能希望首先将 psycopg2 设置为autocommitmode以便您可以使用脚本自己的事务管理。
It'd be nice if psycopg2 offered a smarter mode where it read the file in a statement-at-a-time and sent it to the DB, but at present there's no such mode as far as I know. It'd need a fairly solid parser to do it correctly when faced with $$quoting (and its $delimiter$variant where the deimiter may be any identifier), standard_conforming_strings, E''strings, nested function bodies, etc.
如果 psycopg2 提供一种更智能的模式，它会在一次语句中读取文件并将其发送到数据库，那就太好了，但据我所知，目前没有这种模式。它需要一个相当可靠的解析器来正确处理$$引用（及其$delimiter$变体，其中分隔符可以是任何标识符）standard_conforming_strings、E''字符串、嵌套函数体等。
Note that this will notwork with:
请注意，这不适用于：
- anything containing
psqlbackslash commands - COPY .. FROM STDIN
- verylong input
- 任何包含
psql反斜杠命令的东西 - 复制 .. 从标准输入
- 很长的输入
... and therefore won't work with dumps from pg_dump
...因此不适用于来自的转储 pg_dump
回答by Lisael
I can't reply to comments of the selected answer by lack of reputation, so i'll make an answer to help with the COPYissue.
由于缺乏声誉，我无法回复所选答案的评论，因此我会回答以帮助解决此COPY问题。
Depending on the volume of your DB,pg_dump --insertsoutputs INSERTs instead of COPYs
根据数据库的容量，pg_dump --inserts输出INSERTs 而不是COPYs
