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 main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [[1, "aaa"], [2, "bbb"]] con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function from simplesqlite import SimpleSQLite from simplesqlite.query import Where table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [[1, "aaa"], [2, "bbb"]] con.create_table_from_data_matrix(table_name, ["key", "value"], data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update(table_name, set_query="value = 'ccc'", where=Where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)
from simplesqlite import SimpleSQLite from simplesqlite.sqlquery import SqlQuery table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [ [1, "aaa"], [2, "bbb"], ] con.create_table_from_data_matrix( table_name, attr_name_list=["key", "value"], data_matrix=data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update( table_name, set_query="value = 'ccc'", where=SqlQuery.make_where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)