我在.sql文件中存储了一个PostgreSQL模式。看起来像这样:
I have a PostgreSQL schema stored in .sql file. It looks something like:
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?
我现有的Python代码适用于SQLite数据库:
My existing Python code works for SQLite databases:
# Create database connection self.connection = sqlite3.connect("example.db") # Run database schema with self.connection as cursor: cursor.executescript(open("schema.sql", "r").read())但是psycopg2没有 executescript 方法。那么,我该如何实现呢?
But the psycopg2 doesn't have an executescript method on the cursor. So, how can I achieve this?
推荐答案您可以使用 execute :
with self.connection as cursor: cursor.execute(open("schema.sql", "r").read())尽管您可能想先将psycopg2设置为自动提交模式,以便您可以使用脚本自己的事务管理。
though you may want to set psycopg2 to autocommit mode first so you can use the script's own transaction management.
如果psycopg2提供了一种更智能的模式,那就是它可以一次声明地读取文件并将其发送到数据库,这将是很好的选择,但是目前据我所知,没有这种模式。面对 $$ 引用(及其 $ delimiter $ )时,它需要一个相当可靠的解析器才能正确地执行此操作。变体,其中反斜体可以是任何标识符), standard_conforming_strings , E''字符串,嵌套函数体等。
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.
请注意,这将不可用于:
- 任何包含 psql 反斜杠命令的内容
- COPY .. FROM STDIN
- 非常长输入
- anything containing psql backslash commands
- COPY .. FROM STDIN
- very long input
...因此无法与 pg_dump中的转储一起使用
更多推荐
在Python中的psycopg2中执行.sql模式
发布评论