pymysql

数据库连接(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))