def con_ro(tmpdir):
p = tmpdir.join("tmp_readonly.db")
con = SimpleSQLite(str(p), "w")
con.create_table_from_data_matrix(TEST_TABLE_NAME, ["attr_a", "attr_b"], [[1, 2], [3, 4]])
con.close()
con.connect(str(p), "r")
return con
class SimpleSqliteCilent(object):
def __init__(self):
self.con = SimpleSQLite("pandas_df.sqlite")
def createTableWithDf(self, dataframe, tableName):
self.con.create_table_from_dataframe(dataframe, tableName)
def dropTable(self, tableName):
self.con.drop_table(tableName)
def createTableWithList(self, tableName, columns):
self.con.create_table(tableName, columns)
def insertOneRow(self, tableName, columns):
self.con.insert(tableName, columns)
"""
返回的是tuple形式的结果
"""
def query(self, tableName, whereDict, operation="=", extra=None):
print len(whereDict.keys())
print "---------------"
if len(whereDict.keys()) != 1:
raise NotSupportParamError()
return self.con.select(select="*", table_name=tableName,\
where=SqlQuery.make_where(key=whereDict.keys()[0], value=whereDict[whereDict.keys()[0]],operation=operation),extra=extra).fetchall()
"""
根据where条件更新,目前仅支持1个条件
"""
def update(self, tableName, set_queryDict, whereDict):
print len(whereDict.keys())
print "---------------"
if len(whereDict.keys()) != 1:
raise NotSupportParamError()
set_queryStr = ""
for setKey, setValue in set_queryDict.items():
set_queryStr.join(setKey).join("=").join(setValue)
return self.con.update(tableName,set_query ="", \
where=SqlQuery.make_where(key=whereDict.keys()[0], \
value=whereDict[whereDict.keys()[0]])).fetchall()
"""
插入字典值形式的记录
"""
def insertMany(self, tableName, inert_dictList):
self.con.insert_many(tableName, inert_dictList)
##pass
def __del__(self):
if self.con is not None:
self.con.close()
def con_ro(tmpdir):
p = tmpdir.join("tmp_readonly.db")
con = SimpleSQLite(str(p), "w")
con.create_table_from_data_matrix(TEST_TABLE_NAME, ["attr_a", "attr_b"],
[[1, 2], [3, 4]])
con.close()
con.connect(str(p), "r")
return con
class DatabaseUtil(object):
def __init__(self, db):
self.db = db
self.con = None
@property
def tables(self):
for t in self.db.tables:
yield Table(self.db, self.con, t)
def __enter__(self):
if self.con != None:
print("Warning:connection nesting", self, self.dbkey)
self.con = SimpleSQLite(self.db.path, "a", profile=True)
return self
def __exit__(self, exc_type, exc, traceback):
if self.con == None:
print("Warning:connection not set", self, exc_type, exc,
self.dbkey)
else:
self.con.close()
self.db = None
self.con = None
def con_null(tmpdir):
p = tmpdir.join("tmp_null.db")
con = SimpleSQLite(str(p), "w")
con.close()
return con
# SimpleSQLite
# pip install simplesqlite
from simplesqlite import SimpleSQLite
import pandas as pd
import sys
import sqlite3
# read annotation file
print('reading annotation file...')
anno_df = pd.read_csv(sys.argv[1], sep='\t', index_col=0, header=0)
new_list = ['GeneID'] + list(anno_df.columns)
anno_df = anno_df.reindex(columns=new_list)
anno_df.GeneID = anno_df.index
print('connect gff sqlite3 database...')
con = SimpleSQLite(sys.argv[2])
print('create new table to gff sqlite3 database...')
con.create_table_from_dataframe(anno_df, table_name='anno')
print('create index to sqlite3 database...')
con.create_index_list(
'anno', ['GeneID', 'Nr', 'Swissprot', 'KEGG', 'TrEMBL', 'Interpro'])
con.commit()
con.close()
#!/usr/bin/env python
# encoding: utf-8
from simplesqlite import SimpleSQLite, NullDatabaseConnectionError
import six
con = SimpleSQLite("sample.sqlite", "w")
six.print_("---- connected to a database ----")
con.check_connection()
six.print_("---- disconnected from a database ----")
con.close()
try:
con.check_connection()
except NullDatabaseConnectionError as e:
six.print_(e)
def con_null(tmpdir):
p = tmpdir.join("tmp_null.db")
con = SimpleSQLite(str(p), "w")
con.close()
return con
