我如何在 python 中转储单个 sqlite3 表? [英] how do i dump a single sqlite3 table in python?

问题描述

我只想转储一张表，但从它的外观来看，没有参数.

I would like to dump only one table but by the looks of it, there is no parameter for this.

我找到了这个转储示例，但它适用于数据库中的所有表:

I found this example of the dump but it is for all the tables in the DB:

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3, os

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

推荐答案

您只能复制内存数据库中的单个表:

You can copy only the single table in an in memory db:

import sqlite3

def getTableDump(db_file, table_to_dump):
    conn = sqlite3.connect(':memory:')    
    cu = conn.cursor()
    cu.execute("attach database '" + db_file + "' as attached_db")
    cu.execute("select sql from attached_db.sqlite_master "
               "where type='table' and name='" + table_to_dump + "'")
    sql_create_table = cu.fetchone()[0]
    cu.execute(sql_create_table);
    cu.execute("insert into " + table_to_dump +
               " select * from attached_db." + table_to_dump)
    conn.commit()
    cu.execute("detach database attached_db")
    return "\n".join(conn.iterdump())

TABLE_TO_DUMP = 'table_to_dump'
DB_FILE = 'db_file'

print getTableDump(DB_FILE, TABLE_TO_DUMP)

专业版:简单可靠:您无需重新编写任何库方法，并且您更确信代码与 sqlite3 模块的未来版本兼容.

Pro: Simplicity and reliability: you don't have to re-write any library method, and you are more assured that the code is compatible with future versions of the sqlite3 module.

缺点:您需要将整个表加载到内存中，这取决于表的大小以及可用内存的大小.

Con: You need to load the whole table in memory, which may or may not be a big deal depending on how big the table is, and how much memory is available.

这篇关于我如何在 python 中转储单个 sqlite3 表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！