pymysql和sqlalchemy模組操作mysql資料庫

標籤：ddr   sql   mysql事務   模組   prime   通道   records   col   資料庫連接   

這兩個模組可以直接通過pip安裝：

開啟mysql建立幾個表：

CREATE TABLE `student_age`(    `id` int not null,    `age` int not null,    `sex` ENUM(‘M‘, ‘F‘) not null,    PRIMARY KEY(`id`));INSERT INTO student_age(id, age, sex) VALUES(1, 18, ‘M‘), (2, 26, ‘M‘), (3, 20, ‘F‘);CREATE TABLE `student_name`(    `id` int not null auto_increment,    `name` varchar(10) not null default ‘‘,    `stu_id` int not null,    PRIMARY KEY(`id`),    FOREIGN KEY(`stu_id`) REFERENCES `student_age`(`id`));INSERT INTO student_name(name, stu_id) VALUES(‘Jack‘, 1), (‘Eric‘, 2), (‘Alice‘, 3);

　　

pymysql基本串連樣本：

import pymysql#建立管道conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123456‘, db=‘test‘)#建立遊標cursor = conn.cursor()effect_rows = cursor.execute(‘SELECT * FROM student_name‘)print(‘rows[%d]‘ % effect_rows, cursor.fetchall())#關閉串連conn.close()

　　pymysql插入資料和事物的效果一樣，可以實現復原，自增id佔用，必須提交才會生效：

import pymysql, time#設定資料庫連接參數host = ‘localhost‘port = 3306user = ‘root‘passwd = ‘123456‘db = ‘test‘#建立通道conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)#建立遊標cursor = conn.cursor()#寫sql語句sql = "INSERT INTO student_age(id, age, sex) VALUES(4, 21, ‘F‘)"#執行sql語句effect_row = cursor.execute(sql)#列印影響行print(effect_row)time.sleep(30)#提交資料conn.commit()#關閉通道conn.close()

　　orm操作資料庫建立一張表：

‘‘‘對象關係映射（英語：(Object Relational Mapping，簡稱ORM，或O/RM，或O/R mapping），是一種程式技術，用於實現物件導向程式設計語言裡不同類型系統的資料之間的轉換  。從效果上說，它其實是建立了一個可在程式設計語言裡使用的--“虛擬對象資料庫”。‘‘‘import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringengine = create_engine("mysql+pymysql://root:[email protected]/test", encoding="utf-8", echo=True)#產生orm基類Base = declarative_base()class User(Base):    #表名    __tablename__ = ‘user‘    id = Column(Integer, primary_key=True)    name = Column(String(32))    password = Column(String(64))#建立表結構Base.metadata.create_all(engine)

　　新增資料：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmaker#建立工程engine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘, echo=True)#建立基本處理類Base = declarative_base()#class User(Base):    #表名    __tablename__ = ‘user‘    id = Column(Integer, primary_key=True)    name = Column(String(32))    password = Column(String(64))    #綁定要操作的資料庫Session_class = sessionmaker(bind=engine)#類似於建立遊標Session = Session_class()obj1 = User(id=1, name=‘summer‘, password=‘111111‘)#加入會話工作清單session.add(obj1)#此時資料還未真正寫入print(obj1.id, obj1.name, obj1.password)#寫入資料庫session.commit()

　　查詢和格式化輸出：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import func#建立工程engine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf8‘)#建立基本處理類Base = declarative_base()#class User(Base):    #表名    __tablename__ = ‘user‘    id = Column(Integer, primary_key=True)    name = Column(String(32))    password = Column(String(64))        #格式化輸出查詢結果    def __repr__(self):        return "<%s name:%s>" % (self.id, self.name)    #綁定要操作的資料庫Session_class = sessionmaker(bind=engine)#類似於建立遊標Session = Session_class()#查詢# data = Session.query(User).all()    #查詢全部#條件查詢data = Session.query(User).filter(User.id>1).first()   #擷取第一個#修改data.name = ‘Tom‘data = Session.query(User).filter(User.id>1).first()#撤銷操作# Session.rollback()# print(‘after rollback‘)# data = Session.query(User).filter(User.id>1).first()#提交之後才會作用到資料庫，和mysql事務的的效果一樣# Session.commit()# data = Session.query(User).filter(User.id>1).filter(User.id<4).all()  #擷取區間資料#統計data = Session.query(User).filter(User.name.in_([‘Eric‘, ‘Alice‘])).count()#分組查詢data = Session.query(func.count(User.name), User.name).group_by(User.name).all()print(data)#沒有重寫__repr__之前的訪問方式# print(data[0].id, data[0].name, data[0].password)

　　外鍵表的建立：

"""外鍵表的建立：學生表students課程表days簽到表records，關聯學生和課程表"""import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Enum#建立工程engine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘)#建立基類Base = declarative_base()#建立表結構class Student(Base):    __tablename__ = ‘students‘    id = Column(Integer, nullable=False, primary_key=True)    name = Column(String(10), nullable=False)    sex = Column(Enum(‘F‘, ‘M‘))        def __repr__(self):        return "<id:%s name:%s>" % (self.id, self.name)class Days(Base):    __tablename__ = ‘days‘    id = Column(Integer, nullable=False, primary_key=True)    content = Column(String(32), nullable=False)        def __repr__(self):        return "<day_id:%s content:%s>" % (self.id, self.content)    class Record(Base):    __tablename__ = ‘records‘    id = Column(Integer, nullable=False, primary_key=True)    stu_id = Column(Integer, ForeignKey("students.id"), nullable=False)    day_id = Column(Integer, ForeignKey("days.id"), nullable=False)    status = Column(Enum("Yes", "No"))        def __repr__(self):        return "<id:%s status:%s>" % (self.id, self.status)#產生表Base.metadata.create_all(engine)

　　外鍵表的資料插入：

"""外鍵表的建立：學生表students課程表days簽到表records，關聯學生和課程表"""import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, Enum#建立工程engine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘)#建立基類Base = declarative_base()#建立表結構class Student(Base):    __tablename__ = ‘students‘    id = Column(Integer, nullable=False, primary_key=True)    name = Column(String(10), nullable=False)    sex = Column(Enum(‘F‘, ‘M‘))        def __repr__(self):        return "<id:%s name:%s>" % (self.id, self.name)class Days(Base):    __tablename__ = ‘days‘    id = Column(Integer, nullable=False, primary_key=True)    content = Column(String(32), nullable=False)        def __repr__(self):        return "<day_id:%s content:%s>" % (self.id, self.content)    class Record(Base):    __tablename__ = ‘records‘    id = Column(Integer, nullable=False, primary_key=True)    stu_id = Column(Integer, ForeignKey("students.id"), nullable=False)    day_id = Column(Integer, ForeignKey("days.id"), nullable=False)    status = Column(Enum("Yes", "No"))        def __repr__(self):        return "<id:%s status:%s>" % (self.id, self.status)#產生表Base.metadata.create_all(engine)

　　外鍵表的關聯查詢：

"""外部索引鍵關聯查詢"""import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import Column, Integer, String, Enum, ForeignKey#建立工程engine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘)#建立基類Base = declarative_base()#建立表結構class Students(Base):    __tablename__ = ‘students‘    id = Column(Integer, nullable=False, primary_key=True)    name = Column(String(10), nullable=False)    sex = Column(Enum(‘F‘, ‘M‘))        def __repr__(self):        return "<id:%s name:%s>" % (self.id, self.name)    class Days(Base):    __tablename__ = ‘days‘    id = Column(Integer, nullable=False, primary_key=True)    content = Column(String(32), nullable=False)        def __repr__(self):        return "<id:%s content:%s>" % (self.id, self.content)class Records(Base):    __tablename__ = ‘records‘    id = Column(Integer, nullable=False, primary_key=True)    stu_id = Column(Integer, ForeignKey("students.id"), nullable=False)    day_id = Column(Integer, ForeignKey("days.id"), nullable=False)    status = Column(Enum("Yes", "No"))    students = relationship("Students", backref="students_records")    days = relationship("Days", backref="days_records")    def __repr__(self):        return "<name:%s content:%s status:%s>" % (self.students.name, self.days.content, self.status)#產生表Base.metadata.create_all(engine)#建立會話Session_class = sessionmaker(bind=engine)#建立遊標session = Session_class()data = session.query(Students).filter(Students.name==‘Eric‘).first()print(data.students_records)

　　兩個外部索引鍵關聯到同一張表：

"""兩個外部索引鍵關聯到同一張表"""import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, String, Integer, ForeignKeyfrom sqlalchemy.orm import relationshipengine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘)Base = declarative_base()class Address(Base):    __tablename__ = ‘address‘    id = Column(Integer, primary_key=True, nullable=False)    province = Column(String(10), nullable=True)    city = Column(String(10), nullable=True)    class Users(Base):    __tablename__ = ‘users‘    id = Column(Integer, primary_key=True, nullable=False)    name = Column(String(10), nullable=True)    first_addr_id = Column(Integer, ForeignKey(‘address.id‘))    second_addr_id = Column(Integer, ForeignKey(‘address.id‘))    first_addr = relationship("Address", foreign_keys=[first_addr_id])    second_addr = relationship("Address", foreign_keys=[second_addr_id])        def __repr__(self):        return "<name %s address %s %s>" % (self.name, self.first_addr.province, self.second_addr.province)    Base.metadata.create_all(engine)

　　寫入資料：

"""往表中寫入資料"""import ex5_1from sqlalchemy.orm import sessionmakerSession_class = sessionmaker(bind=ex5_1.engine)session = Session_class()add1 = ex5_1.Address(province=‘HeNan‘, city=‘NanYang‘)add2 = ex5_1.Address(province=‘HeBei‘, city=‘HanDan‘)add3 = ex5_1.Address(province=‘HuNan‘, city=‘YueYang‘)session.add_all([add1, add2, add3])user1 = ex5_1.Users(name=‘Eric‘, first_addr=add1, second_addr=add2)user2 = ex5_1.Users(name=‘Alice‘, first_addr=add2, second_addr=add3)user3 = ex5_1.Users(name=‘Peter‘, first_addr=add3, second_addr=add1)session.add_all([user1, user2, user3])session.commit()

　　查詢：

import ex5_1data = ex5_1.session.query(ex5_1.Users).filter(ex5_1.Users.name==‘Eric‘).first()print(data)

　　多對多外鍵：

#建立表import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, String, Integer, DATE, Table, ForeignKeyfrom sqlalchemy.orm import relationshipengine = create_engine("mysql+pymysql://root:[email protected]/test", encoding=‘utf-8‘)Base = declarative_base()book_m2m_author = Table(    ‘book_m2m_author‘,    Base.metadata,    Column(‘book_id‘, Integer, ForeignKey(‘books.id‘)),    Column(‘author_id‘, Integer, ForeignKey(‘authors.id‘)))class Books(Base):    __tablename__ = ‘books‘    id = Column(Integer, primary_key=True, nullable=True)    book_name = Column(String(20), nullable=False)    publish_time = Column(DATE)    #建立到authors表的映射，關聯關係表book_m2m_author，回查欄位books    authors = relationship("Authors", secondary=book_m2m_author, backref="books")        def __repr__(self):        return "<book_name:%s>" % self.book_name    class Authors(Base):    __tablename__ = ‘authors‘    id = Column(Integer, primary_key=True, nullable=True)    author_name = Column(String(20), nullable=False)    def __repr__(self):        return "<author_name:%s>" % self.author_nameBase.metadata.create_all(engine)

　　建立資料：

#插入資料import ex6_1from sqlalchemy.orm import sessionmaker#建立會話Session_class = sessionmaker(bind=ex6_1.engine)Session = Session_class()b1 = ex6_1.Books(book_name=‘C++ primer plus‘)b2 = ex6_1.Books(book_name=‘Python‘)b3 = ex6_1.Books(book_name=‘Java‘)a1 = ex6_1.Authors(author_name=‘Eric‘)a2 = ex6_1.Authors(author_name=‘Alice‘)a3 = ex6_1.Authors(author_name=‘James‘)b1.authors = [a1, a2]b2.authors = [a2, a3]b3.authors = [a3, a1]Session.add_all([b1, b2, b3, a1, a2, a3])Session.commit()

　　關聯查詢和刪除操作：

#查詢資料import ex6_1from sqlalchemy.orm import sessionmakerSession_class = sessionmaker(bind=ex6_1.engine)Session = Session_class()data = Session.query(ex6_1.Books).filter(ex6_1.Books.book_name==‘Python‘).first()print("book_name:%s author:%s" % (data, data.authors))#刪除資料時不用管book_m2m_author，sqlalchemy會自動刪除#通過書刪除作者# book_obj = Session.query(ex6_1.Books).filter(ex6_1.Books.book_name==‘Python‘).first()# author_obj = Session.query(ex6_1.Authors).filter(ex6_1.Authors.author_name==‘James‘).first()# book_obj.authors.remove(author_obj)#刪除作者時，會把這個作者跟所有書的關聯關係資料也自動刪除data = Session.query(ex6_1.Authors).filter(ex6_1.Authors.author_name==‘Eric‘).first()#從authors裡面刪除這個作者，並從book_m2m_author裡面所有書中刪除這個作者Session.delete(data)#提交修改到資料庫Session.commit()

　　

 

pymysql和sqlalchemy模組操作mysql資料庫