Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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)
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
    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
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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
Ejemplo n.º 11
0
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
Ejemplo n.º 12
0
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
Ejemplo n.º 13
0

# 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'))
Ejemplo n.º 14
0

# 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'))
Ejemplo n.º 15
0
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
Ejemplo n.º 16
0
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