def dbvenn(tablelist,columnid,sqldb,vennengine='evenn',outputfile='defaultdb_venn.pdf',outputdir=None):
if not outputdir:
outputdir=os.getcwd()+'/'
db=SQLiteTools(database =sqldb,stype = 'standard',test =0)
datalis=[]
for table in tablelist:
coldata = db.sqlcolget(tabinfo=[table,columnid],distinct=True)
datalis.append((table,tuple(set(coldata))))
venn_runner(dataset=datalis,outputfilename=outputfile,vennengine=vennengine,outputdir=outputdir)
def dbvenn(tablelist,
columnid,
sqldb,
vennengine='evenn',
outputfile='defaultdb_venn.pdf',
outputdir=None):
if not outputdir:
outputdir = os.getcwd() + '/'
db = SQLiteTools(database=sqldb, stype='standard', test=0)
datalis = []
for table in tablelist:
coldata = db.sqlcolget(tabinfo=[table, columnid], distinct=True)
datalis.append((table, tuple(set(coldata))))
venn_runner(dataset=datalis,
outputfilename=outputfile,
vennengine=vennengine,
outputdir=outputdir)
def testSqlexec(self):
"""
Simple function to run examples
for using the SQLite wrappers
the SQLite database is created if not existing in current working directory
"""
# Prepare the connection to the database
s = SQLiteTools(database='testdb.sqlite', stype='rowquery', test=0)
s.querylimit = -1
# Generate the 'newtab' table in the database
# and fill it with the data
s.tab2sql(tabinfo=('newtab', 'nam', 'yes'), data=[(1, 2), (2, 3), (4, 5), (5, 6), (6, 7), (7, 8)],
typeinfo=['INTEGER', 'TEXT'])
# Query the table i.e. here get the entire table
q = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual(q.data, [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')])
self.assertEqual(q.typeinfo , [u'INTEGER', u'TEXT'])
# write the table to a file
q.write('testfile.txt')
# perform a series of 'SELECT' queries
# with filterinfo (~ 'WHERE) and ignoreinfo (~ WHERE NOT IN)
# the examples use different syntax that all should work
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', ('1','2','3','4')], # ['nam', (1, 2, 3, 4)]
'ignoreinfo': ['yes', '5']})
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', ('1', '2')]}) #
self.assertEqual(q.data, [(1, u'2'), (2, u'3')])
self.assertEqual(q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', ('1', '7'), 'yes', '8']})
self.assertEqual( q.data , [(7, u'8')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
#
q = s.sql2tab(tabinfo=('newtab', 'nam', 'yes'), filterinfo=['nam', '11'])
self.assertEqual( q.data , [])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(tabinfo=('newtab', '*'))
self.assertEqual( q.data , [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'ignoreinfo': ['nam', ('1', '2')]})
self.assertEqual( q.data , [(4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', ('1', '2', '3', '4', '5')],
'ignoreinfo': ['nam', ('1', '2')]})
self.assertEqual( q.data , [(4, u'5'), (5, u'6')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', '1']})
self.assertEqual( q.data , [(1, u'2')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'ignoreinfo': ['nam', '1']})
self.assertEqual( q.data , [(2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(
**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', '1'], 'ignoreinfo': ['yes', ('5', '3')]})
self.assertEqual( q.data , [(1, u'2')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{'tabinfo': ('newtab', 'nam', 'yes'), 'filterinfo': ['nam', ('1', '2', '3', '4')],
'ignoreinfo': ['yes', '5']})
self.assertEqual( q.data , [(1, u'2'), (2, u'3')])
self.assertEqual( q.typeinfo , [u'INTEGER', u'TEXT'])
# Generate the second table
s.tab2sql(['newdiv', 'keyi', 'vali'], dict([(1, 2), (2, 3), (4, 5), (5, 6)]), ['INTEGER', 'TEXT'])
a = s.sqltab2dic(['newdiv', 'vali', 'keyi'])
self.assertEqual( a , {u'3': [2], u'2': [1], u'5': [4], u'6': [5]})
q = s.executesql(query='SELECT * FROM newtab where nam = ?', placeholder=('1',))
self.assertEqual( q.data , [(1, u'2')])
tl = s.get_table_list()
self.assertEqual( tl , [u'newtab', u'newdiv'])
s = SQLiteTools(database='testdb.sqlite', stype='standard', test=0)
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual( [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')] , a.data)
# update example
s.updatevalues(tabinfo=['newtab', 'nam', 'yes'], data=[(8, 9), (98, 11)])
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual( [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8'), (8, u'9'), (98, u'11')] , a.data)
# insert example
s.insertvalues(tabinfo=['newtab', 'nam', 'yes'], data=[(800, 9), (900, 11)])
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual( [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'), (7, u'8'), (8, u'9'), (98, u'11'), (800, u'9'),
(900, u'11')] , a.data)
print 'ALL TESTS PASSED'
def testSqlexec(self):
"""
Simple function to run examples
for using the SQLite wrappers
the SQLite database is created if not existing in current working directory
"""
# Prepare the connection to the database
s = SQLiteTools(database='testdb.sqlite', stype='rowquery', test=0)
s.querylimit = -1
# Generate the 'newtab' table in the database
# and fill it with the data
s.tab2sql(tabinfo=('newtab', 'nam', 'yes'),
data=[(1, 2), (2, 3), (4, 5), (5, 6), (6, 7), (7, 8)],
typeinfo=['INTEGER', 'TEXT'])
# Query the table i.e. here get the entire table
q = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual(q.data, [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'),
(6, u'7'), (7, u'8')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
# write the table to a file
q.write('testfile.txt')
# perform a series of 'SELECT' queries
# with filterinfo (~ 'WHERE) and ignoreinfo (~ WHERE NOT IN)
# the examples use different syntax that all should work
q = s.sql2tab(
**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', ('1', '2', '3',
'4')], # ['nam', (1, 2, 3, 4)]
'ignoreinfo': ['yes', '5']
})
q = s.sql2tab(**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', ('1', '2')]
}) #
self.assertEqual(q.data, [(1, u'2'), (2, u'3')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(
**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', ('1', '7'), 'yes', '8']
})
self.assertEqual(q.data, [(7, u'8')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
#
q = s.sql2tab(tabinfo=('newtab', 'nam', 'yes'),
filterinfo=['nam', '11'])
self.assertEqual(q.data, [])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(tabinfo=('newtab', '*'))
self.assertEqual(q.data, [(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'),
(6, u'7'), (7, u'8')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{
'tabinfo': ('newtab', 'nam', 'yes'),
'ignoreinfo': ['nam', ('1', '2')]
})
self.assertEqual(q.data, [(4, u'5'), (5, u'6'), (6, u'7'), (7, u'8')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(
**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', ('1', '2', '3', '4', '5')],
'ignoreinfo': ['nam', ('1', '2')]
})
self.assertEqual(q.data, [(4, u'5'), (5, u'6')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', '1']
})
self.assertEqual(q.data, [(1, u'2')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(**{
'tabinfo': ('newtab', 'nam', 'yes'),
'ignoreinfo': ['nam', '1']
})
self.assertEqual(q.data, [(2, u'3'), (4, u'5'), (5, u'6'), (6, u'7'),
(7, u'8')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(
**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', '1'],
'ignoreinfo': ['yes', ('5', '3')]
})
self.assertEqual(q.data, [(1, u'2')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
q = s.sql2tab(
**{
'tabinfo': ('newtab', 'nam', 'yes'),
'filterinfo': ['nam', ('1', '2', '3', '4')],
'ignoreinfo': ['yes', '5']
})
self.assertEqual(q.data, [(1, u'2'), (2, u'3')])
self.assertEqual(q.typeinfo, [u'INTEGER', u'TEXT'])
# Generate the second table
s.tab2sql(['newdiv', 'keyi', 'vali'],
dict([(1, 2), (2, 3), (4, 5), (5, 6)]), ['INTEGER', 'TEXT'])
a = s.sqltab2dic(['newdiv', 'vali', 'keyi'])
self.assertEqual(a, {u'3': [2], u'2': [1], u'5': [4], u'6': [5]})
q = s.executesql(query='SELECT * FROM newtab where nam = ?',
placeholder=('1', ))
self.assertEqual(q.data, [(1, u'2')])
tl = s.get_table_list()
self.assertEqual(tl, [u'newtab', u'newdiv'])
s = SQLiteTools(database='testdb.sqlite', stype='standard', test=0)
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual([(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'),
(6, u'7'), (7, u'8')], a.data)
# update example
s.updatevalues(tabinfo=['newtab', 'nam', 'yes'],
data=[(8, 9), (98, 11)])
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual([(1, u'2'), (2, u'3'), (4, u'5'),
(5, u'6'), (6, u'7'), (7, u'8'), (8, u'9'),
(98, u'11')], a.data)
# insert example
s.insertvalues(tabinfo=['newtab', 'nam', 'yes'],
data=[(800, 9), (900, 11)])
a = s.sql2tab(['newtab', 'nam', 'yes'])
self.assertEqual([(1, u'2'), (2, u'3'), (4, u'5'), (5, u'6'),
(6, u'7'), (7, u'8'), (8, u'9'), (98, u'11'),
(800, u'9'), (900, u'11')], a.data)
print 'ALL TESTS PASSED'
