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