python - 将 pandas DataFrame 转储到 SQL 语句

Question

我需要将 pandas DataFrame 对象转换为一系列重现该对象的 SQL 语句。

例如，假设我有一个 DataFrame 对象:

>>> df = pd.DataFrame({'manufacturer': ['Audi', 'Volkswagen', 'BMW'], 
                       'model': ['A3', 'Touareg', 'X5']})
>>> df
  manufacturer    model
0         Audi       A3
1   Volkswagen  Touareg
2          BMW       X5

我需要将其转换为以下 SQL 表示形式(不完全相同):

CREATE TABLE "Auto" (
"index" INTEGER,
  "manufacturer" TEXT,
  "model" TEXT
);
INSERT INTO Auto (manufacturer, model) VALUES ('Audi', 'A3'), ('Volkswagen', 'Touareg'), ('BMW', 'X5');

幸运的是，pandas DataFrame 对象具有 to_sql() 方法，该方法允许通过 SQLAlchemy 引擎将整个 DataFrame 转储到数据库。我决定为此使用 SQLite 内存数据库:

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite://', echo=False)  # Turning echo to True just logs SQL statements, I'd avoid parsing this logs
>>> df.to_sql(name='Auto', con=engine)

我现在被困住了。我无法将 SQLite 内存数据库转储到 SQL 语句，也找不到将 SQL 语句转储到文件而不是执行它们的 sqlalchemy 驱动程序。

有没有办法将发送到 SQLAlchemy 引擎的所有查询作为 SQL 语句转储到文件中？

到目前为止我的解决方案并不优雅:

>>> from sqlalchemy import MetaData
>>> meta = MetaData()
>>> meta.reflect(bind=engine)
>>> print(pd.io.sql.get_schema(df, name='Auto') + ';')
CREATE TABLE "Auto" (
"manufacturer" TEXT,
  "model" TEXT
);
>>> print('INSERT INTO Auto ({}) VALUES\n{};'.format(', '.join([repr(c) for c in df.columns]), ',\n'.join([str(row[1:]) for row in engine.execute(meta.tables['Auto'].select())])))
INSERT INTO Auto ('manufacturer', 'model') VALUES
('Audi', 'A3'),
('Volkswagen', 'Touareg'),
('BMW', 'X5');

我实际上更喜欢不需要手动构建 SQL 语句的解决方案。

Answer 1

SQLite 实际上允许将整个数据库转储为一系列带有 dump command 的 SQL 语句。 。此功能也可在 SQLite 的 python DB-API 接口(interface)中使用:sqlite3，特别是通过 connection object's iterdump() method 。据我所知，SQLAlchemy不提供此功能。

因此，要将 pandas DataFrame 转储到一系列 SQL 语句，需要首先将其转储到内存 SQLite 数据库，然后使用 iterdump() 方法转储该数据库:

from sqlalchemy import create_engine    

engine = create_engine('sqlite://', echo=False)
df.reset_index().to_sql(name=table_name, con=engine)  # reset_index() is needed to preserve index column in dumped data

with engine.connect() as conn:
    for line in conn.connection.iterdump():
        stream.write(line)
        stream.write('\n')

engine().connect().connection 允许获取 raw DBAPI connection .