python——连接mysql

Python操作MySQL

对于Python操作MySQL主要有两种方式：

• 原生模块 pymsql
• ORM框架 SQLAchemy

一、pymysql操作MySQL

1、执行sql语句

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='xxx', db='xingedb')     # 创建连接

cursor = conn.cursor()     # 创建游标，为了日后的回滚

effect_row = cursor.execute('select password from cmdb_userinfo where username = %s;',[name,])      # 执行SQL，并返回收影响行数

# 执行SQL，并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
  
# 执行SQL，并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])

conn.commit()    # 提交，不然无法保存新建或者修改的数据

cursor.close()     # 关闭游标

conn.close()    # 关闭连接

2、创建新数据并获取新创建数据的自增ID

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])    # 执行添加操作
conn.commit()
cursor.close()
conn.close()
  
new_id = cursor.lastrowid    # 获取最新自增ID

3、获取查询数据

import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts")
  
# 获取第一行数据
row_1 = cursor.fetchone()
  
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
# row_3 = cursor.fetchall()
  
conn.commit()
cursor.close()
conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode='relative')  # 相对当前位置移动
• cursor.scroll(2,mode='absolute') # 相对绝对位置移动

4、fetch数据类型

　　关于默认获取的数据是元组(abc,)类型，如果想要或者字典类型的数据，即：

import pymysql
  
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
  

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)    # 游标设置为字典类型
r = cursor.execute("call p1()")
  
result = cursor.fetchone()
  
conn.commit()
cursor.close()
conn.close()

二、SQLAchemy（ORM）操作MySQL

使用python创建一个表

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

engine=create_engine('mysql+pymysql://root:123@localhost/xingedb',encoding='utf-8')

Base=declarative_base()    #生成orm基类

class user(Base):
    __tablename__='user'    #创建的表名
    id=Column(Integer,primary_key=True)
    name=Column(String(32))
    password=Column(String(64))

Base.metadata.create_all(engine)    #创建表结构
Session_class=sessionmaker(bind=engine)    #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session=Session_class()    #生成实例

Session.commit()    #保存操作

增、删、改、查

user_obj=User(name="x54256",password="123")    #增

Session.add(user_obj)

Session.query(User).filter(User.id == 2).delete()    #删

data= Session.query(User).filter_by(name="x54256").first()    #查
print(data)

输出是一个obj： <__main__.User object at 0x000002CD45131D30>

在类下面加下面的代码，就会显示具体的数据了

def __repr__(self):
    return "<User(name='%s',  password='%s')>" % (
        self.name, self.password)

data= Session.query(User).filter_by(name="x54256").first()    #改
data.name='alex'
data.password='abc'

Session.commit()

其他

#获取所有数据
print(Session.query(User.name,User.id).all() )

#多条件查询
objs = Session.query(User).filter(User.id>0).filter(User.id<7).all()

#统计和分组
Session.query(User).filter(User.name.like("Ra%")).count()

外键关联

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

engine=create_engine('mysql+pymysql://root:123@localhost/xingedb',encoding='utf-8')
Base=declarative_base()

class Student(Base):
    __tablename__='student'
    id=Column(Integer,primary_key=True)
    name=Column(String(32),nullable=False)
    register_date=Column(DATE,nullable=False)

    def __repr__(self):
        return '<name:%s,register_date:%s >'%(self.name,self.register_date)

class StudyRecord(Base):
    __tablename__='study_record'
    id=Column(Integer, primary_key=True)
    day=Column(Integer,nullable=False)
    status=Column(String(32),nullable=False)
    stu_id=Column(Integer,ForeignKey('student.id'))
    student=relationship("Student",backref="my_study_record")

    def __repr__(self):
        return '<name:%s,day:%s,status:%s,stu_id:%s>'%(self.student.name,self.day,self.status,self.stu_id)

Base.metadata.create_all(engine)
Session_class=sessionmaker(bind=engine)
session=Session_class()

# s1=Student(name='alex',register_date="2014-05-21")
# s2=Student(name='jack',register_date="2014-04-21")
# s3=Student(name='rain',register_date="2014-03-21")
# s4=Student(name='eriv',register_date="2012-03-21")
#
# study_obj1=StudyRecord(day=1,status='No',stu_id=1)
# study_obj2=StudyRecord(day=2,status='Yes',stu_id=1)
# study_obj3=StudyRecord(day=2,status='No',stu_id=2)
# study_obj4=StudyRecord(day=3,status='Yes',stu_id=2)
# study_obj5=StudyRecord(day=1,status='Yes',stu_id=3)
#
# session.add_all([s1,s2,s3,s4,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])

stu_obj=session.query(Student).filter(Student.name=='alex').first()
print(stu_obj.my_study_record)


session.commit()

stu_obj=session.query(Student).filter(Student.name=='alex').first()
print(stu_obj.my_study_record)

多外键关联

many_wk.py

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

Base = declarative_base()

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    billing_address_id = Column(Integer, ForeignKey("address.id"))
    shipping_address_id = Column(Integer, ForeignKey("address.id"))

    billing_address = relationship("Address", foreign_keys=[billing_address_id])    #一定要这样写要不然，添加的时候回找不到
    shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(32))
    city = Column(String(32))
    state = Column(String(32))
    def __repr__(self):
        return '<%s,%s,%s>'%(self.state,self.city,self.street)

engine=create_engine('mysql+pymysql://root:123@localhost/xingedb',encoding='utf-8')
Base.metadata.create_all(engine)    #创建表结构

多外键.py

from day_11 import many_wk
from sqlalchemy.orm import sessionmaker

Session_class=sessionmaker(bind=many_wk.engine)    #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session=Session_class()    #生成实例

# addr1=many_wk.Address(street='Tiantongyuan',city='Changping',state='BJ')
# addr2=many_wk.Address(street='Wudaokou',city='Haidian',state='BJ')
# addr3=many_wk.Address(street='Yanjiao',city='Langfang',state='HB')
#
# Session.add_all([addr1,addr2,addr3])
#
# c1=many_wk.Customer(name='alex',billing_address=addr1,shipping_address=addr2)
# c2=many_wk.Customer(name='x54256',billing_address=addr3,shipping_address=addr3)
# Session.add_all([c1,c2])
#
# Session.commit()    #保存操作

data=Session.query(many_wk.Customer).filter(many_wk.Customer.name=='alex').first()

print(data.name,data.billing_address,data.shipping_address)

多对多关系
orm_m2m.py

from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

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 Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=book_m2m_author,backref='books')

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))

    def __repr__(self):
        return self.name

engine=create_engine('mysql+pymysql://root:710130520a@localhost/xingedb?charset=utf8')  #这样写就可以输入中文啦
Base.metadata.create_all(engine)    #创建表结构

多对多.py

from day_11 import orm_m2m
from sqlalchemy.orm import sessionmaker



Session_class = sessionmaker(bind=orm_m2m.engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

s = Session_class()  # 生成session实例

# b1 = orm_m2m.Book(name="跟Alex学Python")
# b2 = orm_m2m.Book(name="跟Alex学把妹")
# b3 = orm_m2m.Book(name="跟Alex学装逼")
# b4 = orm_m2m.Book(name="跟Alex学开车")
#
# a1 = orm_m2m.Author(name="Alex")
# a2 = orm_m2m.Author(name="Jack")
# a3 = orm_m2m.Author(name="Rain")
#
# b1.authors = [a1, a2]   #设定关系
# b2.authors = [a1, a2, a3]
#
# s.add_all([b1, b2, b3, b4, a1, a2, a3])


print('--------通过书表查关联的作者---------')
book_obj = s.query(orm_m2m.Book).filter_by(name="跟Alex学Python").first()
print(book_obj.name, book_obj.authors)

print('--------通过作者表查关联的书---------')
author_obj = s.query(orm_m2m.Author).filter_by(name="Alex").first()
print(author_obj.name, author_obj.books)


s.commit()