阅读量:0
``
python,import sqlite3,,class SQLiteHelper:, def __init__(self, db_name):, self.conn = sqlite3.connect(db_name), self.cursor = self.conn.cursor(),, def execute(self, sql):, self.cursor.execute(sql), self.conn.commit(),, def fetchall(self, sql):, self.cursor.execute(sql), return self.cursor.fetchall(),, def close(self):, self.cursor.close(), self.conn.close(),
``import sqlite3 class SQLiteDatabase: def __init__(self, db_name): self.conn = sqlite3.connect(db_name) self.cursor = self.conn.cursor() def create_table(self, table_name, columns): column_definitions = ', '.join([f"{col_name} {col_type}" for col_name, col_type in columns.items()]) sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({column_definitions});" self.cursor.execute(sql) self.conn.commit() def insert_data(self, table_name, data): keys = ', '.join(data.keys()) values = ', '.join(['?' for _ in data.values()]) sql = f"INSERT INTO {table_name} ({keys}) VALUES ({values});" self.cursor.execute(sql, tuple(data.values())) self.conn.commit() def update_data(self, table_name, set_columns, where_conditions): set_clause = ', '.join([f"{col} = ?" for col in set_columns.keys()]) where_clause = ' AND '.join([f"{col} = ?" for col in where_conditions.keys()]) sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause};" self.cursor.execute(sql, tuple(set_columns.values()) + tuple(where_conditions.values())) self.conn.commit() def delete_data(self, table_name, conditions): condition_clause = ' AND '.join([f"{col} = ?" for col in conditions.keys()]) sql = f"DELETE FROM {table_name} WHERE {condition_clause};" self.cursor.execute(sql, tuple(conditions.values())) self.conn.commit() def query_data(self, table_name, columns='*', conditions=None): if conditions: condition_clause = ' AND '.join([f"{col} = ?" for col in conditions.keys()]) sql = f"SELECT {columns} FROM {table_name} WHERE {condition_clause};" self.cursor.execute(sql, tuple(conditions.values())) else: sql = f"SELECT {columns} FROM {table_name};" self.cursor.execute(sql) return self.cursor.fetchall() def close(self): self.conn.close() Example usage: if __name__ == "__main__": db = SQLiteDatabase("test.db") db.create_table("users", {"id": "INTEGER PRIMARY KEY", "name": "TEXT", "age": "INTEGER"}) db.insert_data("users", {"id": 1, "name": "Alice", "age": 25}) db.insert_data("users", {"id": 2, "name": "Bob", "age": 30}) db.update_data("users", {"age": 26}, {"id": 1}) result = db.query_data("users", "name, age", {"id": 1}) print(result) # [('Alice', 26)] db.delete_data("users", {"id": 2}) all_users = db.query_data("users") print(all_users) # [(1, 'Alice', 26)] db.close()