1、使用SQLite：Python内置
- 创建连接sqlite3.connect
- 创建游标conn.cursor
- 通过游标执行sql语句cursor.execute(‘select * from user where id = ?’, (‘1’,)). ?为占位符
- 处理数据库返回结果cursor.rowcount，cursor.fetchall
- 关闭游标
- 提交事务：对表有修改（增删改）时需要提交
- 关闭连接
代码演示：打印出成绩在60-100之间的用户名
import os, sqlite3
db_file = ‘db/school.db’
if os.path.isfile(db_file):
os.remove(db_file)
# 初始数据:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute(‘create table user(id varchar(20) primary key, name varchar(20), score int)’)
cursor.execute(r”insert into user values (‘A-001’, ‘Adam’, 95)”)
cursor.execute(r”insert into user values (‘A-002’, ‘Bart’, 62)”)
cursor.execute(r”insert into user values (‘A-003’, ‘Lisa’, 78)”)
def get_score_in(low, high):
cursor.execute(r”select name from user where score>? and score<? order by score” , (low,high)) # 用 sql排序
# cursor.execute(r”s elect * from user where score>? and score<?” , (low,high))
result = cursor.fetchall()
# result = sorted(result,key=lambda t:t[2]) # 用Python排序
# names = [row[1] for row in result] # 遍历result，每一次遍历结果记为row ,取所有的row[1]
print(result)
get_score_in(60,100) # 方法的定义必须在使用之前
cursor.close()
conn.commit() conn.close() |
2、使用MySQL（只有第一步连接数据库与sqlite不同）
- 创建连接mysql.connector.connnect(user,password,database)
- 创建游标conn.cursor
- 通过游标执行sql语句cursor.execute(‘select * from user where id = %s’, (‘1’,)). %s为占位符
- 处理数据库返回结果cursor.rowcount，cursor.fetchall
- 关闭游标
- 提交事务
- 关闭连接
# -*- coding: utf-8 -*-
import mysql.connector
#
初始数据
:
conn = mysql.connector.connect(
user=
‘root’,
password=
‘password’,
database=
‘test’)
#
创建游标来执行
SQL
语句
cursor = conn.cursor()
# cursor.execute(‘create table user(id varchar(20) primary key, name varchar(20), score int)’)
# cursor.execute(r”insert into user values (‘A-001’, ‘Adam’, 95)”)
# cursor.execute(r”insert into user values (‘A-002’, ‘Bart’, 62)”)
# cursor.execute(r”insert into user values (‘A-003’, ‘Lisa’, 78)”)
def get_score_in(low, high):
cursor.execute(
r”select name from user where score>%s and score<%s order by score”, (low, high))
#
用
sql
排序
,MySQL
的占位符是
%s,SQLite
的占位符是
?
# cursor.execute(r”select * from user where score>? and score<?” , (low,high))
result = cursor.fetchall()
# result = sorted(result,key=lambda t:t[2]) #
用
Python
排序
# names = [row[1] for row in result] #
遍历
result
，每一次遍历结果记为
row ,
取所有的
row[1]
print(result)
get_score_in(
60,
100)
#
方法的定义必须在使用之前
cursor.close()
conn.commit()
conn.close()
|
3、使用SQLAlchemy:提供SQL工具包和ORM（Object-Relational Mapping，实现关系数据库表与对象的映射）工具包
ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转换。
- 创建基础类
- 定义类（类名为数据库表名）
- 初始化数据库连接create_engine()
- 由sessionmaker创建DBsession类型（视为数据库连接，类似于mysql.connnector.connect）
- 创建session对象（类型为DBsession）
- 创建新的类对象
- 添加到session
- session提交
- session关闭
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from sqlalchemy import * # from sqlalchemy import Column,String,INT,create_engine
# 创建对象的基类
Base = declarative_base()
class User(Base):
__tablename__ =
‘user’
#
与数据库表名一致
id = Column(String(
20),
primary_key=
True)
name = Column(String(
20))
score = Column(INT)
#
初始化数据库连接
engine = create_engine(
‘mysql+mysqlconnector://root:[email protected]:3306/test’)
DBSession = sessionmaker(
bind=engine)
# DBSession
相当于
mysql.connector.connection
session = DBSession()
# new_user = User(id=’2′,name=’Bob’,score=98)
#
# session.add(new_user)
user = session.query(User).filter(User.id==
‘5’).one()
print(user.name)
session.commit()
#
一定要记得提交
session.close()
声明：学习廖雪峰的Python教程学习笔记，感谢Micheal Liao.
|
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/11568.html
