def main(): con = SimpleSQLite("pandas_df.sqlite") con.create_table_from_dataframe( pandas.DataFrame([[0, 0.1, "a"], [1, 1.1, "bb"], [2, 2.2, "ccc"]], columns=["id", "value", "name"]), table_name="pandas_df", )
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()
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function from simplesqlite import SimpleSQLite import pandas con = SimpleSQLite("pandas_df.sqlite") con.create_table_from_dataframe(pandas.DataFrame( [ [0, 0.1, "a"], [1, 1.1, "bb"], [2, 2.2, "ccc"], ], columns=['id', 'value', 'name']), table_name="pandas_df")
# 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 __future__ import print_function from simplesqlite import SimpleSQLite import pandas con = SimpleSQLite("pandas_df.sqlite") con.create_table_from_dataframe(pandas.DataFrame( [ [0, 0.1, "a"], [1, 1.1, "bb"], [2, 2.2, "ccc"], ], columns=['id', 'value', 'name'] ), table_name="pandas_df")
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function import pandas from simplesqlite import SimpleSQLite con = SimpleSQLite("pandas_df.sqlite") con.create_table_from_dataframe( pandas.DataFrame( [[0, 0.1, "a"], [1, 1.1, "bb"], [2, 2.2, "ccc"]], columns=["id", "value", "name"] ), table_name="pandas_df", )
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function import pandas from simplesqlite import SimpleSQLite con = SimpleSQLite("pandas_df.sqlite") con.create_table_from_dataframe( pandas.DataFrame([[0, 0.1, "a"], [1, 1.1, "bb"], [2, 2.2, "ccc"]], columns=["id", "value", "name"]), table_name="pandas_df", )
print('reading expression file...') anno_df = pd.read_csv(sys.argv[1], sep='\t', index_col=0, header=0) new_list = ['Gene_id'] + list(anno_df.columns) anno_df = anno_df.reindex(columns=new_list) anno_df.Gene_id = anno_df.index print('connect sqlite3 database...') if os.path.exists(sys.argv[2]): os.remove(sys.argv[2]) print("file exists! we will detele it.") con = SimpleSQLite(sys.argv[2]) print('create new table to sqlite3 database...') #con.create_table_from_dataframe(anno_df, table_name='exp',add_primary_key_column=1) con.create_table_from_dataframe(anno_df, table_name='exp', primary_key='Gene_id') # Migration print('create index to sqlite3 database...') con.create_index_list('exp', [ 'Gene_id', ]) con.commit() con.close()