python sqlite进行ORM
首先，请确保您已经安装了Python和SQLite。然后安装Python的SQLite扩展模块：
pip install pysqlite3
接下来，创建一个Python文件并导入需要的库：
import sqlite3 from sqlite3 import Error
定义一个函数，用于连接到SQLite数据库。如果数据库不存在，则创建一个新数据库。在这个函数中，还可以执行一些初始SQL语句以创建数据表：
def create_connection(): conn = None; try: conn = sqlite3.connect('example.db') print(sqlite3.version) except Error as e: print(e) finally: if conn: # create a users table create_users_table(conn) # create an orders table create_orders_table(conn) # close the database connection conn.close()
现在我们需要创建一些函数，来执行SQL语句创建数据表：
def create_users_table(conn): try: cursor = conn.cursor() sql = '''CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, email TEXT NOT NULL, password TEXT NOT NULL );''' cursor.execute(sql) except Error as e: print(e) def create_orders_table(conn): try: cursor = conn.cursor() sql = '''CREATE TABLE IF NOT EXISTS orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, product_name TEXT, price REAL, FOREIGN KEY (user_id) REFERENCES users (id) );''' cursor.execute(sql) except Error as e: print(e)
现在我们可以定义一个ORM模型来映射数据表和Python中的对象：
class User: def __init__(self, username, email, password): self.username = username self.email = email self.password = password def __str__(self): return f'{self.username}, {self.email}, {self.password}'
我们还可以为每个对象添加一些方法，例如保存（insert）和查找（select）：
class User: def __init__(self, username, email, password): self.username = username self.email = email self.password = password def __str__(self): return f'{self.username}, {self.email}, {self.password}' def save(self): try: conn = sqlite3.connect('example.db') cursor = conn.cursor() sql = '''INSERT INTO users (username, email, password) VALUES (?, ?, ?)''' cursor.execute(sql, (self.username, self.email, self.password)) conn.commit() print(f'User {self.username} saved successfully.') except Error as e: print(e) finally: if conn: conn.close() @staticmethod def select_all(): try: conn = sqlite3.connect('example.db') cursor = conn.cursor() sql = '''SELECT * FROM users''' cursor.execute(sql) rows = cursor.fetchall() for row in rows: print(row) except Error as e: print(e) finally: if conn: conn.close()
现在，我们可以使用这些对象和方法来操作数据库：
# create a new user object user = User('john', 'john@example.com', 'password123') # save the user to the database user.save() # select all users from the database User.select_all()
这就是一个简单的Python和SQLite进行ORM的示例代码。
