pymysql

迈不过友情╰ 2022-02-27 23:54 186阅读 0赞

数据库连接(pymysql)

class ConnectMysql(object):
        def __init__(self, host, user, database, password=None, port=3306,
                     charset='utf8'):
            self.host = host
            self.user = user
            self.password = password
            self.database = database
            self.db = pymysql.connect(
                host=self.host, user=self.user, password=self.password,
                database=self.database, port=port, charset=charset)
            self.cursor = self.db.cursor()
    
        def select(self, sql, *args):
            u"""select操作"""
            self.cursor.execute(sql, args)
            res = self.cursor.fetchall()
            return res
    
        def execute(self, sql, *args):
            u"""insert, update, delete"""
            try:
                self.cursor.execute(sql, args)
                self.db.commit()
            except Exception:
                self.db.rollback()
                LOG.info("execute failed: " + sql)
    
        def close(self):
            u"""关闭数据库"""
            self.cursor.close()
            self.db.close()

sqlalchemy

#!/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:123456@localhost:3306/test",encoding="UTF-8",echo=True) “echo=True” 显示底层的ＳＱＬ语句
    engine = create_engine("mysql+pymysql://root:123456@localhost: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))