def test_fromsqlite3_cachetag():
"""Test the fromsqlite3 cachetag function."""
# initial data
f = NamedTemporaryFile(delete=False)
data = (('a', 1), ('b', 2), ('c', 2.0))
connection = sqlite3.connect(f.name)
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
tbl = fromsqlite3(f.name, 'select * from foobar')
tag1 = tbl.cachetag()
# update the data
modata = (('d', 1), ('e', 2), ('f', 2.0))
c = connection.cursor()
for _ in range(100):
for row in modata:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
tag2 = tbl.cachetag()
assert tag2 != tag1, (tag2, tag1)
def test_fromsqlite3_cachetag_strict():
"""Test the fromsqlite3 cachetag function under strict conditions."""
# initial data
f = NamedTemporaryFile(delete=False)
data = (('a', 1), ('b', 2), ('c', 2.0))
connection = sqlite3.connect(f.name)
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
prevdef = petl.io.defaultsumfun
petl.io.defaultsumfun = adler32sum
tbl = fromsqlite3(f.name, 'select * from foobar', checksumfun=adler32sum)
tag1 = tbl.cachetag()
# update the data
connection.execute('update foobar set bar = ? where foo = ?', (42, 'a'))
connection.commit()
tag2 = tbl.cachetag()
assert tag2 != tag1, (tag2, tag1)
# reset default
petl.io.defaultsumfun = prevdef
def test_fromsqlite3_cachetag():
"""Test the fromsqlite3 cachetag function."""
# initial data
f = NamedTemporaryFile(delete=False)
data = (('a', 1),
('b', 2),
('c', 2.0))
connection = sqlite3.connect(f.name)
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
tbl = fromsqlite3(f.name, 'select * from foobar')
tag1 = tbl.cachetag()
# update the data
modata = (('d', 1),
('e', 2),
('f', 2.0))
c = connection.cursor()
for _ in range(100):
for row in modata:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
tag2 = tbl.cachetag()
assert tag2 != tag1, (tag2, tag1)
def test_fromsqlite3():
"""Test the fromsqlite3 function."""
# initial data
f = NamedTemporaryFile(delete=False)
data = (('a', 1),
('b', 2),
('c', 2.0))
connection = sqlite3.connect(f.name)
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
connection.close()
# test the function
actual = fromsqlite3(f.name, 'select * from foobar')
expect = (('foo', 'bar'),
('a', 1),
('b', 2),
('c', 2.0))
print list(actual)
ieq(expect, actual, cast=tuple)
ieq(expect, actual, cast=tuple) # verify can iterate twice
def test_fromsqlite3_cachetag_strict():
"""Test the fromsqlite3 cachetag function under strict conditions."""
# initial data
f = NamedTemporaryFile(delete=False)
data = (('a', 1),
('b', 2),
('c', 2.0))
connection = sqlite3.connect(f.name)
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
prevdef = petl.io.defaultsumfun
petl.io.defaultsumfun = adler32sum
tbl = fromsqlite3(f.name, 'select * from foobar', checksumfun=adler32sum)
tag1 = tbl.cachetag()
# update the data
connection.execute('update foobar set bar = ? where foo = ?', (42, 'a'))
connection.commit()
tag2 = tbl.cachetag()
assert tag2 != tag1, (tag2, tag1)
# reset default
petl.io.defaultsumfun = prevdef
def table_data(self, query):
'''Return a petl container for a data table'''
import petl
query = query.strip().lower()
if 'select' not in query:
query = "select * from {} ".format(query)
return petl.fromsqlite3(self.database.path, query) #@UndefinedVariable
def test_fromsqlite3_withargs():
# initial data
data = (("a", 1), ("b", 2), ("c", 2.0))
connection = sqlite3.connect(":memory:")
c = connection.cursor()
c.execute("create table foobar (foo, bar)")
for row in data:
c.execute("insert into foobar values (?, ?)", row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection, "select * from foobar where bar > ? and bar < ?", (1, 3))
expect = (("foo", "bar"), ("b", 2), ("c", 2.0))
ieq(expect, actual)
ieq(expect, actual) # verify can iterate twice
def test_fromsqlite3_connection():
"""Test the fromsqlite3 function."""
# initial data
data = (('a', 1), ('b', 2), ('c', 2.0))
connection = sqlite3.connect(':memory:')
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection, 'select * from foobar')
expect = (('foo', 'bar'), ('a', 1), ('b', 2), ('c', 2.0))
print list(actual)
ieq(expect, actual, cast=tuple)
ieq(expect, actual, cast=tuple) # verify can iterate twice
def test_fromsqlite3_withargs():
# initial data
data = (('a', 1), ('b', 2), ('c', 2.0))
connection = sqlite3.connect(':memory:')
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection,
'select * from foobar where bar > ? and bar < ?',
(1, 3))
expect = (('foo', 'bar'), ('b', 2), ('c', 2.0))
ieq(expect, actual)
ieq(expect, actual) # verify can iterate twice
def test_fromsqlite3_connection():
"""Test the fromsqlite3 function."""
# initial data
data = (("a", 1), ("b", 2), ("c", 2.0))
connection = sqlite3.connect(":memory:")
c = connection.cursor()
c.execute("create table foobar (foo, bar)")
for row in data:
c.execute("insert into foobar values (?, ?)", row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection, "select * from foobar")
expect = (("foo", "bar"), ("a", 1), ("b", 2), ("c", 2.0))
print list(actual)
ieq(expect, actual, cast=tuple)
ieq(expect, actual, cast=tuple) # verify can iterate twice
def test_fromsqlite3_withargs():
# initial data
data = (('a', 1),
('b', 2),
('c', 2.0))
connection = sqlite3.connect(':memory:')
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection, 'select * from foobar where bar > ? and bar < ?', (1, 3))
expect = (('foo', 'bar'),
('b', 2),
('c', 2.0))
ieq(expect, actual)
ieq(expect, actual) # verify can iterate twice
def test_fromsqlite3_connection():
"""Test the fromsqlite3 function."""
# initial data
data = (('a', 1),
('b', 2),
('c', 2.0))
connection = sqlite3.connect(':memory:')
c = connection.cursor()
c.execute('create table foobar (foo, bar)')
for row in data:
c.execute('insert into foobar values (?, ?)', row)
connection.commit()
c.close()
# test the function
actual = fromsqlite3(connection, 'select * from foobar')
expect = (('foo', 'bar'),
('a', 1),
('b', 2),
('c', 2.0))
print(list(actual))
ieq(expect, actual, cast=tuple)
ieq(expect, actual, cast=tuple) # verify can iterate twice
# tosqlite3
table = [['foo', 'bar'],
['a', 1],
['b', 2],
['c', 2]]
from petl import tosqlite3, look
look(table)
# by default, if the table does not already exist, it will be created
tosqlite3(table, 'test.db', 'foobar')
# look what it did
from petl import fromsqlite3
look(fromsqlite3('test.db', 'select * from foobar'))
# appendsqlite3
moredata = [['foo', 'bar'],
['d', 7],
['e', 9],
['f', 1]]
from petl import appendsqlite3, look
look(moredata)
appendsqlite3(moredata, 'test.db', 'foobar')
# look what it did
from petl import look, fromsqlite3
look(fromsqlite3('test.db', 'select * from foobar'))
# tosqlite3
table = [['foo', 'bar'],
['a', 1],
['b', 2],
['c', 2]]
from petl import tosqlite3, look
look(table)
# by default, if the table does not already exist, it will be created
tosqlite3(table, 'test.db', 'foobar')
# look what it did
from petl import fromsqlite3
look(fromsqlite3('test.db', 'select * from foobar'))
# appendsqlite3
moredata = [['foo', 'bar'],
['d', 7],
['e', 9],
['f', 1]]
from petl import appendsqlite3, look
look(moredata)
appendsqlite3(moredata, 'test.db', 'foobar')
# look what it did
from petl import look, fromsqlite3
look(fromsqlite3('test.db', 'select * from foobar'))
os.remove('example.db')
import petl as etl
import sqlite3
# set up a database to demonstrate with
data = [['a', 1], ['b', 2], ['c', 2.0]]
connection = sqlite3.connect('example.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
table = etl.fromsqlite3('example.db', 'select * from foobar')
table
# tosqlite3()
##############
os.remove('example.db')
import petl as etl
table1 = [['foo', 'bar'], ['a', 1], ['b', 2], ['c', 2]]
_ = etl.tosqlite3(table1, 'example.db', 'foobar', create=True)
# look what it did
table2 = etl.fromsqlite3('example.db', 'select * from foobar')
table2
import sqlite3
# set up a database to demonstrate with
data = [['a', 1],
['b', 2],
['c', 2.0]]
connection = sqlite3.connect('example.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
table = etl.fromsqlite3('example.db', 'select * from foobar')
table
# tosqlite3()
##############
os.remove('example.db')
import petl as etl
table1 = [['foo', 'bar'],
['a', 1],
['b', 2],
['c', 2]]
_ = etl.tosqlite3(table1, 'example.db', 'foobar', create=True)
# look what it did
