FilesExpand file tree

python-stdlib

/

dump.py

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

70 lines (62 loc) · 2.76 KB

python-stdlib

/

dump.py

Copy path

Top

File metadata and controls

• Code
• Blame

70 lines (62 loc) · 2.76 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

# Mimic the sqlite3 console shell's .dump command

# Author: Paul Kippes <kippesp@gmail.com>

# Every identifier in sql is quoted based on a comment in sqlite

# documentation "SQLite adds new keywords from time to time when it

# takes on new features. So to prevent your code from being broken by

# future enhancements, you should normally quote any identifier that

# is an English language word, even if you do not have to."

def _iterdump(connection):

"""

Returns an iterator to the dump of the database in an SQL text format.

Used to produce an SQL dump of the database. Useful to save an in-memory

database for later restoration. This function should not be called

directly but instead called from the Connection method, iterdump().

"""

cu = connection.cursor()

yield('BEGIN TRANSACTION;')

# sqlite_master table contains the SQL CREATE statements for the database.

q = """

SELECT "name", "type", "sql"

FROM "sqlite_master"

WHERE "sql" NOT NULL AND

"type" == 'table'

ORDER BY "name"

"""

schema_res = cu.execute(q)

for table_name, type, sql in schema_res.fetchall():

if table_name == 'sqlite_sequence':

yield('DELETE FROM "sqlite_sequence";')

elif table_name == 'sqlite_stat1':

yield('ANALYZE "sqlite_master";')

elif table_name.startswith('sqlite_'):

continue

# NOTE: Virtual table support not implemented

#elif sql.startswith('CREATE VIRTUAL TABLE'):

# qtable = table_name.replace("'", "''")

# yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\

# "VALUES('table','{0}','{0}',0,'{1}');".format(

# qtable,

# sql.replace("''")))

else:

yield('{0};'.format(sql))

# Build the insert statement for each row of the current table

table_name_ident = table_name.replace('"', '""')

res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident))

column_names = [str(table_info[1]) for table_info in res.fetchall()]

q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format(

table_name_ident,

",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names))

query_res = cu.execute(q)

for row in query_res:

yield("{0};".format(row[0]))

# Now when the type is 'index', 'trigger', or 'view'

q = """

SELECT "name", "type", "sql"

FROM "sqlite_master"

WHERE "sql" NOT NULL AND

"type" IN ('index', 'trigger', 'view')

"""

schema_res = cu.execute(q)

for name, type, sql in schema_res.fetchall():

yield('{0};'.format(sql))

yield('COMMIT;')