标签归档:dump

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

问题:使用Python sqlite3 API的表,数据库模式,转储等的列表

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

.tables
.dump

使用Python sqlite3 API。

有没有类似的东西?

For some reason I can’t find a way to get the equivalents of sqlite’s interactive shell commands:

.tables
.dump

using the Python sqlite3 API.

Is there anything like that?


回答 0

您可以通过查询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
)

You can fetch the list of tables and schemata by querying the SQLITE_MASTER table:

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
)

回答 1

在Python中:

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

当心我的其他答案。使用熊猫有更快的方法。

In Python:

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

Watch out for my other answer. There is a much faster way using pandas.


回答 2

在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()

The FASTEST way of doing this in python is using Pandas (version 0.16 and up).

Dump one table:

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

Dump all tables:

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

回答 3

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

SELECT * FROM sqlite_master;

I’m not familiar with the Python API but you can always use

SELECT * FROM sqlite_master;

回答 4

这是一个简短的python程序,用于打印出这些表的表名和列名(后接python2。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.")

Here’s a short and simple python program to print out the table names and the column names for those tables (python 2. python 3 follows).

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

(EDIT: I have been getting periodic vote-ups on this, so here is the python3 version for people who are finding this answer)

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

回答 5

显然,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)

Apparently the version of sqlite3 included in Python 2.6 has this ability: 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

经过很多摆弄之后,我在sqlite文档中找到了一个更好的答案,它列出了表的元数据,甚至是附加的数据库。

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

关键信息是前缀table_info,而不是带有附件句柄名称的my_table。

After a lot of fiddling I found a better answer at sqlite docs for listing the metadata for the table, even attached databases.

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

The key information is to prefix table_info, not my_table with the attachment handle name.


回答 7

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

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)

If someone wants to do the same thing with Pandas

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)

回答 8

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

Check out here for dump. It seems there is a dump function in the library sqlite3.


回答 9

#!/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"
#!/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"

回答 10

我已经在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)');

I’ve implemented a sqlite table schema parser in PHP, you may check here: https://github.com/c9s/LazyRecord/blob/master/src/LazyRecord/TableParser/SqliteTableDefinitionParser.php

You can use this definition parser to parse the definitions like the code below:

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