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 simpledb(): return_array = [] temp_dict = {} print("In the simpledb function") table_name = "sample_table" con = SimpleSQLite("simple.sqlite", "w") print("after con") # create table ----- data_matrix = [[1, 1.1, "aaa", 1, 1], [2, 2.2, "bbb", 2.2, 2.2], [3, 3.3, "ccc", 3, "ccc"]] con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix, ) con.insert(table_name, record={ "attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge", }) # display data type for each column in the table ----- print(con.schema_extractor.fetch_table_schema(table_name).dumps()) # display values in the table ----- print("records:") key_array = [ "attr_a", "attr_b", "attr_c", "attr_d", "attr_e", ] result = con.select(select="*", table_name=table_name) print(type(result)) for record in result.fetchall(): print(record) temp_dict = dict(zip(key_array, record)) return_array.append(temp_dict) temp_dict = [] print("Last line of the simpledb function:") return json.dumps(return_array)
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]] ) # insert namedtuple SampleTuple = namedtuple("SampleTuple", "attr_a attr_b attr_c attr_d attr_e") con.insert(table_name, record=[7, 7.7, "fff", 7.77, "bar"]) con.insert_many( table_name, records=[(8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge")], ) # print result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
def main(): table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]] ) con.insert( table_name, record={"attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge"}, ) con.insert_many( table_name, records=[ {"attr_a": 5, "attr_b": 5.5, "attr_c": "eee", "attr_d": 5.55, "attr_e": "foo"}, {"attr_a": 6, "attr_c": "fff"}, ], ) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function from collections import namedtuple from simplesqlite import SimpleSQLite table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, attr_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix=[[1, 1.1, "aaa", 1, 1]]) SampleTuple = namedtuple("SampleTuple", "attr_a attr_b attr_c attr_d attr_e") con.insert(table_name, insert_record=[7, 7.7, "fff", 7.77, "bar"]) con.insert_many(table_name, insert_record_list=[ (8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge"), ]) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)
from __future__ import print_function from simplesqlite import SimpleSQLite table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, ["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], [[1, 1.1, "aaa", 1, 1]]) con.insert( table_name, record={ "attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge" }, ) con.insert_many( table_name, records=[ { "attr_a": 5, "attr_b": 5.5, "attr_c": "eee", "attr_d": 5.55, "attr_e": "foo" }, {
from simplesqlite import SimpleSQLite table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, attr_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix=[[1, 1.1, "aaa", 1, 1]]) con.insert( table_name, insert_record={ "attr_a": 4, "attr_b": 4.4, "attr_c": "ddd", "attr_d": 4.44, "attr_e": "hoge", }) con.insert_many( table_name, insert_record_list=[ { "attr_a": 5, "attr_b": 5.5, "attr_c": "eee", "attr_d": 5.55, "attr_e": "foo", }, {
#!/usr/bin/env python # encoding: utf-8 from __future__ import print_function from collections import namedtuple from simplesqlite import SimpleSQLite table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") con.create_table_from_data_matrix( table_name, attr_name_list=["attr_a", "attr_b", "attr_c", "attr_d", "attr_e"], data_matrix=[[1, 1.1, "aaa", 1, 1]]) SampleTuple = namedtuple( "SampleTuple", "attr_a attr_b attr_c attr_d attr_e") con.insert(table_name, insert_record=[7, 7.7, "fff", 7.77, "bar"]) con.insert_many( table_name, insert_record_list=[ (8, 8.8, "ggg", 8.88, "foobar"), SampleTuple(9, 9.9, "ggg", 9.99, "hogehoge"), ]) result = con.select(select="*", table_name=table_name) for record in result.fetchall(): print(record)