使用Python sqlite3 API的表,db模式,转储等的列表

由于某种原因,我找不到一种方法来获得sqlite的交互式shell命令的等价物:

.tables
.dump

使用Python sqlite3 API.

有什么相似的吗？

1> Davoud Tagha..：

---

在Python中:

con = sqlite3.connect('database.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

留意我的其他答案.使用pandas有一种更快捷的方式.

---

对于要复制/粘贴的用户:确保`cursor.close()`和`db.close()`

2> converter42..：

---

您可以通过查询SQLITE_MASTER表来获取表和模式列表:

sqlite> .tab
job         snmptarget  t1          t2          t3        
sqlite> select name from sqlite_master where type = 'table';
job
t1
t2
snmptarget
t3

sqlite> .schema job
CREATE TABLE job (
    id INTEGER PRIMARY KEY,
    data VARCHAR
);
sqlite> select sql from sqlite_master where type = 'table' and name = 'job';
CREATE TABLE job (
    id INTEGER PRIMARY KEY,
    data VARCHAR
)

3> Davoud Tagha..：

---

在python中执行此操作的最快方法是使用Pandas(版本0.16及更高版本).

转储一张桌子:

db = sqlite3.connect('database.db')
table = pd.read_sql_query("SELECT * from table_name", db)
table.to_csv(table_name + '.csv', index_label='index')

转储所有表格:

import sqlite3
import pandas as pd


def to_csv():
    db = sqlite3.connect('database.db')
    cursor = db.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    for table_name in tables:
        table_name = table_name[0]
        table = pd.read_sql_query("SELECT * from %s" % table_name, db)
        table.to_csv(table_name + '.csv', index_label='index')
    cursor.close()
    db.close()

---

对于要复制/粘贴的用户：请确保分别使用“ cursor.close（）”和“ db.close（）”。

4> finnw..：

---

我不熟悉Python API但你可以随时使用

SELECT * FROM sqlite_master;

5> checkit..：

---

显然,Python 2.6中包含的sqlite3版本具有以下功能:http://docs.python.org/dev/library/sqlite3.html

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

6> RufusVS..：

---

这是一个简短的python程序,用于打印表名和这些表的列名.

import sqlite3

db_filename = 'database.sqlite'
newline_indent = '\n   '

db=sqlite3.connect(db_filename)
db.text_factory = str
cur = db.cursor()

result = cur.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
table_names = sorted(zip(*result)[0])
print "\ntables are:"+newline_indent+newline_indent.join(table_names)

for table_name in table_names:
    result = cur.execute("PRAGMA table_info('%s')" % table_name).fetchall()
    column_names = zip(*result)[1]
    print ("\ncolumn names for %s:" % table_name)+newline_indent+(newline_indent.join(column_names))

db.close()
print "\nexiting."