python自动数据库_Python操作Mysql 数据库(自动化运维-16)

Python操作MySQL方法

原生模块 pymsql

ORM框架 SQLAchemy

pymysql 操作方法简介

pymysql是Python中操作MySQL的模块

安装

pip3 install pymysql

操作

执行sql

import pymysql

#创建连接

conn = pymysql.connect(host="120.27.136.208",port=3306,user="wpic",passwd="wpic1234",db="wpic")

#创建游标

cursor = conn.cursor()

#执行 SQL，并返回受影响的行数，是数字

cursor.execute("show tables")

cursor.execute("create table family (id int unsigned auto_increment,"

"name VARCHAR(20) not null,"

"age int not null,"

"primary KEY(id))"

"ENGINE=InnoDB DEFAULT CHARSET=utf8") # 执行的就是 sql 原生语句

#提交数据

conn.commit()

#关闭游标

cursor.close()

#关闭连接

conn.close()

获取新创建数据自增ID

import pymysql

#创建连接

conn = pymysql.connect(host="120.27.136.208",port=3306,user="wpic",passwd="wpic1234",db="wpic")

#创建游标

cursor = conn.cursor()

# 执行多条语句

cursor.executemany("insert into family(name,age)values(%s,%s)",[('bushaoxun',33),('guolijuan',32)])

#提交数据

conn.commit()

#关闭游标

cursor.close()

#关闭连接

conn.close()

# 获取最新自增ID

ID = cursor.lastrowid

print(ID)

获取查询数据

import pymysql

#创建连接

conn = pymysql.connect(host="120.27.136.208",port=3306,user="wpic",passwd="wpic1234",db="wpic")

#创建游标

cursor = conn.cursor()

cursor.execute("select * from family")

#获取第一行数据

row_1=cursor.fetchone()

print(row_1)

#获取前 n 行数据

row_2=cursor.fetchmany(3)

print(row_2)

#获取所有数据

row_3=cursor.fetchall() #默认获取的数据是元祖类型

print(row_3)

#关闭游标

cursor.close()

#关闭连接

conn.close()

SQLAchemy

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

SQLAlchemy本身无法操作数据库，其必须依赖pymysql等第三方插件,根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python

mysql+mysqldb://:@[:]/

pymysql

mysql+pymysql://:@/[?]

安装 sqlalchemy

pip3 install sqlalchemy

sqlalchemy 基本使用

创建表

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8', echo=True)

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

Base.metadata.create_all(engine) #创建表结构

插入数据

import sqlalchemy

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://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8', echo=True)

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

#Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库进行会话的类

Session = Session_class() #生成会话实例

user_obj = User(name="wpic",password="1234.com") #生成要创建的数据对象,也就是实例.

user_obj1 = User(name="beijing",password="123.com")

Session.add_all([user_obj,user_obj1]) #把要创建的数据对象添加到这个 Session 里

Session.commit() # 统一提交，创建数据

查询数据

import sqlalchemy

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://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)# 查询后生成对象默认返回的数据

#Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库进行会话的类

Session = Session_class() #生成会话实例

user = Session.query(User).filter_by(name="zhanshen").first() #返回查询语句的第一条记录，生成一个对象

print(user)

user = Session.query(User).filter_by(name="zhanshen").all() #返回查询语句的所有记录

print(user)

修改数据

import sqlalchemy

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://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)# 查询后生成对象默认返回的数据

#Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库进行会话的类

Session = Session_class() #生成会话实例

user = Session.query(User).filter_by(name="zhanshen").first() #返回查询语句的第一条记录，生成一个对象

user.name = "wpic" # 修改用户名 zhanshen 到 wpic

Session.commit()

回滚的命令

Session.rollback() #回滚数据

多条件查询

Session_class = sessionmaker(bind=engine) #创建与数据库进行会话的类

Session = Session_class() #生成会话实例

result = Session.query(User).filter(User.name.in_(['bushaoxun','wpic'])).all()

print(result)

result1 = Session.query(User).filter(User.id>0).filter(User.id<4).all()

print(result1)

统计和分组

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)# 查询后生成对象默认返回的数据

#Base.metadata.create_all(engine) #创建表结构

Session_class = sessionmaker(bind=engine) #创建与数据库进行会话的类

Session = Session_class() #生成会话实例

result = Session.query(User).filter(User.name.like("wp%")).count() # 统计有多少个以wp开头的

print(result)

result1 = Session.query(User.name,func.count(User.name)).group_by(User.name).all() #分组统计姓名有多少个

print(result1)

Session.commit()

外键关联

创建外键关联并添加数据

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)

class Address(Base):

__tablename__ = 'email_address'

id = Column(Integer,primary_key=True)

email_address = Column(String(40),nullable=False) #设置不允许为 null

user_id = Column(Integer,ForeignKey('user.id'))

user = relationship("User",backref="addresses") #允许在user表里通过addresses字段反查出所有在 email_address表里的关联项

def __repr__(self):

return "email: '%s'" % self.email_address

Session_class = sessionmaker(bind=engine)

Session = Session_class()

address_obj = Address(email_address="363640619@qq.com",user_id=1)

address_obj1 = Address(email_address="bushaoxun@wpic.co",user_id=3)

address_obj2 = Address(email_address="shaoxun@wpic.co",user_id=5)

Session.add_all([address_obj,address_obj1,address_obj2])

# Base.metadata.create_all(engine)# 创建表结构

Session.commit()

通过外键关联查找数据

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)

class Address(Base):

__tablename__ = 'email_address'

id = Column(Integer,primary_key=True)

email_address = Column(String(40),nullable=False) #设置不允许为 null

user_id = Column(Integer,ForeignKey('user.id'))

user = relationship("User",backref="addresses") #允许在user表里通过addresses字段反查出所有在 email_address表里的关联项

def __repr__(self):

return "email: '%s'" % self.email_address

Session_class = sessionmaker(bind=engine)

Session = Session_class()

obj = Session.query(User).first()

print(obj.addresses[0].email_address) # 在 user 表中通过 addresses 字段查找 email_address表的字段

address_obj = Session.query(Address).first() # 在 email__address 表中通过 user 字段 查找 user 表的字段

print(address_obj,address_obj.user)

多外键关联

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

class User(Base):

__tablename__ = "user" #表名

id = Column(Integer,primary_key=True)

name = Column(String(40))

password = Column(String(60))

def __repr__(self):

return "User(name='%s',password='%s')" % (self.name,self.password)

class Address(Base):

__tablename__ = 'email_address'

id = Column(Integer,primary_key=True)

email_address = Column(String(40),nullable=False) #设置不允许为 null

info_id = Column(Integer,ForeignKey('user.id'))

login_id = Column(Integer,ForeignKey('user.id'))

info = relationship('User',foreign_keys=[info_id]) # 明确说明关联的是哪个外键，否则会报错.

login = relationship('User',foreign_keys=[login_id])

def __repr__(self):

return "email: '%s'" % self.email_address

Base.metadata.create_all(engine)# 创建表结构

多对多关系

更多信息猛击这里

建立数据表

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column,Integer,String,Table

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic",

encoding='utf-8')

Base = declarative_base() # 生成 orm基类

book_mtm_author = Table("book_mtm_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(60))

authors = relationship("Author",secondary=book_mtm_author,backref="books")

def __repr__(self):

return self.name

class Author(Base):

__tablename__ = 'authors'

id = Column(Integer,primary_key=True)

name = Column(String(40))

def __repr__(self):

return self.name

Base.metadata.create_all(engine) #创建表结构

处理中文问题

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式。

engine = create_engine("mysql+pymysql://wpic:wpic1234@127.0.0.1/wpic?charset=utf8",echo=True)