我通常使用psycopg2/postgres,但这是我经常使用的基本DB类,以Python的SQLite为例:
import sqlite3
class Database:
def __init__(self, name):
self._conn = sqlite3.connect(name)
self._cursor = self._conn.cursor()
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.close()
@property
def connection(self):
return self._conn
@property
def cursor(self):
return self._cursor
def commit(self):
self.connection.commit()
def close(self, commit=True):
if commit:
self.commit()
self.connection.close()
def execute(self, sql, params=None):
self.cursor.execute(sql, params or ())
def fetchall(self):
return self.cursor.fetchall()
def fetchone(self):
return self.cursor.fetchone()
def query(self, sql, params=None):
self.cursor.execute(sql, params or ())
return self.fetchall()
这将允许您使用Database
类,通常类似于db = Database('db_file.sqlite)
或with
语句:
with Database('db_file.sqlite') as db:
# do stuff
当with
语句退出时,连接将自动提交并关闭.
然后,可以将经常使用的特定查询封装在方法中,并使其易于访问.例如,如果您正在处理交易记录,您可以使用一种方法按日期获取它们:
def transactions_by_date(self, date):
sql = "SELECT * FROM transactions WHERE transaction_date = ?"
return self.query(sql, (date,))
下面是一些示例代码,我们在其中创建一个表,添加一些数据,然后将其读回:
with Database('my_db.sqlite') as db:
db.execute('CREATE TABLE comments(pkey INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR, comment_body VARCHAR, date_posted TIMESTAMP)')
db.execute('INSERT INTO comments (username, comment_body, date_posted) VALUES (?, ?, current_date)', ('tom', 'this is a comment'))
comments = db.query('SELECT * FROM comments')
print(comments)
我希望这有帮助!