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()
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)