使用Python sqlite3 API的表列表，数据库模式，转储等

出于某种原因，我找不到一种方法来获得等同于sqlite的交互式shell命令:

.tables
.dump

使用Python sqlite3 API。

有类似的东西吗?

262784 次浏览
小开

我不熟悉Python API，但你总是可以使用

SELECT * FROM sqlite_master;
小开
最佳答案

你可以通过查询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
)
小开

检查在这里的转储。在sqlite3库中似乎有一个转储函数。

小开

显然，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)
小开

在Python中:

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

注意我的另一个回答。用熊猫有一个更快的方法。

小开

经过大量的摆弄，我在sqlite文档找到了一个更好的答案，用于列出表的元数据，甚至附加的数据库。

meta = cursor.execute("PRAGMA table_info('Job')")
for r in meta:
print r

关键信息是添加table_info前缀，而不是添加附件句柄名的my_table。

小开

我已经在PHP中实现了一个sqlite表模式解析器，你可以在这里检查:https://github.com/c9s/LazyRecord/blob/master/src/LazyRecord/TableParser/SqliteTableDefinitionParser.php

你可以使用这个定义解析器来解析定义，如下所示:

$parser = new SqliteTableDefinitionParser;
$parser->parseColumnDefinitions('x INTEGER PRIMARY KEY, y DOUBLE, z DATETIME default \'2011-11-10\', name VARCHAR(100)');
小开

在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()
小开 
#!/usr/bin/env python
# -*- coding: utf-8 -*-


if __name__ == "__main__":


import sqlite3


dbname = './db/database.db'
try:
print "INITILIZATION..."
con = sqlite3.connect(dbname)
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
for tbl in tables:
print "\n########  "+tbl[0]+"  ########"
cursor.execute("SELECT * FROM "+tbl[0]+";")
rows = cursor.fetchall()
for row in rows:
print row
print(cursor.fetchall())
except KeyboardInterrupt:
print "\nClean Exit By user"
finally:
print "\nFinally"
小开

下面是一个简短而简单的python程序，用于打印出这些表的表名和列名(python 2。接下来是Python 3)。

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."

(编辑:我已经得到了关于这个问题的定期投票，所以这里是python3版本，供找到这个答案的人使用)

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(list(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 = list(zip(*result))[1]
print (("\ncolumn names for %s:" % table_name)
+newline_indent
+(newline_indent.join(column_names)))


db.close()
print ("\nexiting.")
小开

如果有人想对熊猫做同样的事情

import pandas as pd
import sqlite3
conn = sqlite3.connect("db.sqlite3")
table = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table'", conn)
print(table)
小开

如果您只想打印出数据库中的所有表和列，有些人可能会发现我的函数很有用。

在循环中，我用LIMIT 0查询每个TABLE，因此它只返回头部信息而不返回所有数据。你可以从中得到一个空df，并使用可迭代对象df。列来打印每个列名。

conn = sqlite3.connect('example.db')
c = conn.cursor()


def table_info(c, conn):
'''
prints out all of the columns of every table in db
c : cursor object
conn : database connection object
'''
tables = c.execute("SELECT name FROM sqlite_master WHERE type='table';").fetchall()
for table_name in tables:
table_name = table_name[0] # tables is a list of single item tuples
table = pd.read_sql_query("SELECT * from {} LIMIT 0".format(table_name), conn)
print(table_name)
for col in table.columns:
print('\t' + col)
print()


table_info(c, conn)

Results will be:


table1
column1
column2


table2
column1
column2
column3


etc.

提交答案