ORM

pymysql处理事件时：

# pymysql-event
import pymysql

# 涉及到写操作的需要commit
db = pymysql.connect(host="localhost",user="root",password="123456",
database="studentInfo",port=3306,charset="utf8")

cursor = db.cursor()
try:
    ins = "insert into t1 values(2,'小姐姐',88)"
    cursor.execute(ins)
    upd = "update t1 set score=100 where name='小姐姐'"
    cursor.execute(upd)
    dele = "delete from t1 where id=1"
    cursor.execute(dele)
    db.commit()
except Exception as e:
    db.rollback()
    print("Failed:",e)

cursor.close()
db.close()

# pymysql-event
import pymysql

# 涉及到读操作的不需要commit
db = pymysql.connect(host="localhost",user="root",password="123456",
database="studentInfo",port=3306,charset="utf8")

cursor = db.cursor()
try:
    sel = "select * from t1 name like '小姐姐%'"
    cursor.execute(sel)
    res = cursor.fetchall()
except Exception as e:
    db.rollback()
    print("Failed:",e)

cursor.close()
db.close()

ORM 框架

#!/usr/bin/env python 
# -*- coding:utf-8 -*-

from sqlalchemy import Integer,String,Column,create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

#与数据库连接对象
# engine = create_engine("mysql+pymysql://root:[email protected]:3306/test",encoding="UTF-8",echo=True) “echo=True” 显示底层的ＳＱＬ语句
engine = create_engine("mysql+pymysql://root:[email protected]:3306/test",encoding="UTF-8",echo=True)
#创建session会话类
DBsession = sessionmaker(bind=engine)
session = DBsession()  #session实例
#创建ＯＲＭ基类
Base = declarative_base()

#继承基类
class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(20))
    phnumber = Column(String(11),unique=True)

    def __init__(self,name,phnumber):
        self.name = name
        self.phnumber = phnumber


Base.metadata.create_all(engine)
# student1 = Student("Lily","15114827769")
# student2 = Student("Lucy","17671228934")
# session.add_all([student1,student2])
# session.commit()

st = session.query(Student).filter_by(id=1).first()
print("姓名：%s,年龄：%s" % (st.name,st.phnumber))