/
sqlite3.py
186 lines (144 loc) · 5.85 KB
/
sqlite3.py
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
from __future__ import absolute_import, print_function, division, \
unicode_literals
# standard library dependencies
import sqlite3
from petl.compat import string_types, next
# internal dependencies
from petl.util import RowContainer
quotechar = '"'
def _quote(s):
# crude way to sanitise table and field names
# conform with the SQL-92 standard. See http://stackoverflow.com/a/214344
return quotechar + s.replace(quotechar, quotechar+quotechar) + quotechar
def fromsqlite3(source, query, *args, **kwargs):
"""Provides access to data from an :mod:`sqlite3` database file via a given
query. E.g.::
>>> import sqlite3
>>> # set up a database to demonstrate with
... data = [['a', 1],
... ['b', 2],
... ['c', 2.0]]
>>> connection = sqlite3.connect('test.db')
>>> c = connection.cursor()
>>> _ = c.execute('drop table if exists foobar')
>>> _ = c.execute('create table foobar (foo, bar)')
>>> for row in data:
... _ = c.execute('insert into foobar values (?, ?)', row)
...
>>> connection.commit()
>>> c.close()
>>> # now demonstrate the petl.fromsqlite3 function
... from petl import look, fromsqlite3
>>> foobar = fromsqlite3('test.db', 'select * from foobar')
>>> look(foobar)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a' | 1 |
+-------+-------+
| 'b' | 2 |
+-------+-------+
| 'c' | 2.0 |
+-------+-------+
Either a database file name or a connection object can be given as the
first argument.
"""
return Sqlite3View(source, query, *args, **kwargs)
class Sqlite3View(RowContainer):
def __init__(self, source, query, *args, **kwargs):
self.source = source
self.query = query
self.args = args
self.kwargs = kwargs
# setup the connection
if isinstance(self.source, string_types):
self.connection = sqlite3.connect(self.source)
self.connection.row_factory = sqlite3.Row
elif isinstance(self.source, sqlite3.Connection):
self.connection = self.source
else:
raise Exception('source argument must be filename or connection; '
'found %r' % self.source)
def __iter__(self):
cursor = self.connection.cursor()
cursor.execute(self.query, *self.args, **self.kwargs)
fields = [d[0] for d in cursor.description]
yield tuple(fields)
for row in cursor:
yield row # don't wrap
# tidy up
cursor.close()
def tosqlite3(table, filename_or_connection, tablename, create=False,
commit=True):
"""Load data into a table in an :mod:`sqlite3` database. Note that if
the database table exists, it will be truncated, i.e., all
existing rows will be deleted prior to inserting the new
data. E.g.::
>>> table1 = [['foo', 'bar'],
... ['a', 1],
... ['b', 2],
... ['c', 2]]
>>> from petl import tosqlite3, look
>>> _ = tosqlite3(table1, 'test.db', 'foobar', create=True)
>>> # look what it did
... from petl import fromsqlite3
>>> table2 = fromsqlite3('test.db', 'select * from foobar')
>>> look(table2)
+-------+-------+
| 'foo' | 'bar' |
+=======+=======+
| 'a' | 1 |
+-------+-------+
| 'b' | 2 |
+-------+-------+
| 'c' | 2 |
+-------+-------+
If the table does not exist and ``create=True`` then a table will be created
using the field names in the table header. However, note that no type
specifications will be included in the table creation statement and so
column type affinities may be inappropriate.
Either a database file name or a connection object can be given as the
second argument.
"""
return _tosqlite3(table, filename_or_connection, tablename, create=create,
commit=commit, truncate=True)
def _tosqlite3(table, filename_or_connection, tablename, create=False,
commit=True, truncate=False):
if isinstance(filename_or_connection, string_types):
conn = sqlite3.connect(filename_or_connection)
elif isinstance(filename_or_connection, sqlite3.Connection):
conn = filename_or_connection
else:
raise Exception('filename_or_connection argument must be filename or '
'connection; found %r' % filename_or_connection)
tablename = _quote(tablename)
it = iter(table)
fields = next(it)
fieldnames = map(str, fields)
colnames = [_quote(n) for n in fieldnames]
cursor = conn.cursor()
if create: # force table creation
cursor.execute(u'DROP TABLE IF EXISTS %s' % tablename)
cursor.execute(u'CREATE TABLE %s (%s)'
% (tablename, ', '.join(colnames)))
if truncate:
# truncate table
cursor.execute(u'DELETE FROM %s' % tablename)
# insert rows
placeholders = ', '.join(['?'] * len(colnames))
insertquery = u'INSERT INTO %s VALUES (%s);' % (tablename, placeholders)
cursor.executemany(insertquery, it)
# tidy up
cursor.close()
if commit:
conn.commit()
return conn # in case people want to re-use it or close it
def appendsqlite3(table, filename_or_connection, tablename, commit=True):
"""Load data into a table in an :mod:`sqlite3` database. As
:func:`tosqlite3` except the table must exist and is not truncated before
loading.
Either a database file name or a connection object can be given as the
second argument.
"""
return _tosqlite3(table, filename_or_connection, tablename, create=False,
commit=commit, truncate=False)