python操作mysql数据库

Python DB-API操作数据库

Python的标准数据库接口是Python DB-API，它为开发人员提供了数据库应用程序编程接口。 Python数据库接口支持多种数据库：mysql、oracle。

不同的数据库需要下载不同的DB API模块。例如，如果您需要访问Oracle数据库和MySQL数据，则需要下载Oracle和MySQL数据库模块。

Python 的 DB-API 实现了大多数数据库的接口。使用它连接各个数据库后，就可以对各个数据库进行同样的操作。

使用python DB-API操作流程

可见，接口操作数据的核心是创建连接对象。

import mysql.connector
conn = mysql.connector.connect(
	user='root', 
	password='password', 
	database='db' , 
	auth_plugin='mysql_native_password'
)

如上导入了mysql.connector第三方库，需要安装pip install mysql-connector和pip install mysql-connector-python --allow-external mysql-connector-python

另外MySQLdb库也可以实现同样的功能。pip install MySQLdb 是python2版本。

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )
# 其他都一样

这是四个最基础的参数，最后一个auth_plugin是为解决高版本mysql8.0更改加密密码的措施。

PyMySQL也是python3的DB-API之一，库名不同但操作方法都是一样的。

# 安装
pip3 install PyMySQL

import pymysql
 
# 打开数据库连接
db = pymysql.connect(host='localhost',
                     user='testuser',
                     password='test123',
                     database='TESTDB')
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL 查询 
cursor.execute("SELECT VERSION()")
 
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

操作方法是一样的。

connecttion对象

cursor对象

常用方法

close():关闭此游标对象
fetchone():得到结果集的下一行
fetchmany([size = cursor.arraysize]):得到结果集的下几行
fetchall():得到结果集中剩下的所有行
excute(sql[, args]):执行一个数据库查询或命令
excutemany(sql, args):执行多个数据库查询或命令

游标对象用于执行sql语句。

共同属性
connection:创建此游标对象的数据库连接
arraysize:使用fetchmany()方法一次取出多少条记录，默认为1
lastrowid:返回最后一个数据的id

其他方法
__iter__()：创建一个可迭代对象（可选）
next()：获取结果集的下一行（如果支持迭代的话）
nextset()：移到下一个结果集（如果支持的话）
callproc(func[,args])：调用一个存储过程
setinputsizes(sizes)：设置输入最大值（必须有，但具体实现是可选的）
setoutputsizes(sizes[,col])：设置大列 fetch 的最大缓冲区大小

其他属性
description：返回游标活动状态（包含7个元素的元组）：（name, type_code, display_size, internal_size, precision, scale, null_ok）只有 name 和 type_cose 是必需的
rowcount：最近一次 execute() 创建或影响的行数
messages：游标执行后数据库返回的信息元组（可选）
rownumber：当前结果集中游标所在行的索引（起始行号为 0）

https://www.jb51.net/article/104820.htm
http://www.zzvips.com/article/105426.html

增删查改案例

查看

import mysql.connector

conn = mysql.connector.connect(user='root', password='baby5429', db='db1', auth_plugin='mysql_native_password')
cursor = conn.cursor()


def method():
    cursor.execute('select * from user')
    values = cursor.fetchall()

    print(values)
    cursor.close()

    conn.close()


if __name__ == "__main__":
    method()

excute(sql[, args]):执行一个数据库查询或命令,excutemany(sql, args):执行多个数据库查询或命令fetchone()需要借助循环。fetchall()查询所有。

删除

def method():
    cursor.execute('delete from user where id = 4')
    values = cursor.rowcount

    print(values)

    cursor.close()

    # 提交事物
    conn.commit()
    conn.close()

DML操作最重要的就是提交事物。rowcount参数返回影响行数。

# 编程式事物管理
def method():
    cursor.execute('delete from user where id = 4')
    values = cursor.rowcount

    print(values)

    cursor.close()
    try:
        num = 5 / 0
        conn.commit()
    except Exception:
        conn.rollback()
    finally:
        conn.close

增加
动态参数的占位符是%s。而不是python的其他占位符。

def method():
    cursor.execute('insert into user(username,password) values(%s,%s)',("zhansan1","123456"))
    values = cursor.rowcount

    print(values)

    cursor.close()
    try:
        #num = 5 / 0
        conn.commit()
    except Exception:
        conn.rollback()
    finally:
        conn.close

改和增等DML语句都是只更改sql语句，然后提交事务即可，动态参数的占位符是%s

SQLAlchemy实现ORM映射

ORM是一种将数据库查询对象映射到对象的技术，而SQLAlchemy是python实现该技术的一个框架。

python的DDL操作查询的结构如下：

显然是python的列表，每个元素是一个元组（有序不重复）

自动将数据库表中的一行记录转换为对象：

自定义转换

# user对象
class User(object):
    def __init__(self, id, username, password):
        self.__id = id
        self.__username = username
        self.__password = password

    def getUserName(self):
        return self.__username

    def setUserName(self, username):
        self.__username = username
        return None

    def getPassword(self):
        return self.__password

    def serPassword(self, password):
        self.__password = password
        return None

import mysql.connector
from mysqlTest.User import User
conn = mysql.connector.connect(user='root', password='baby5429', db='db1', auth_plugin='mysql_native_password')
cursor = conn.cursor()


def method():
    cursor.execute('select * from user')
    values = cursor.fetchall()

    print(values)
    cursor.close()
    try:
        # 存储结构
        users = []

        # 定义orm模型
        for i in values:
            user = User(i[0],i[1],[2])
            users.append(user)
    except Exception:
        conn.rollback()
    finally:
        conn.close

    print(users)


if __name__ == "__main__":
    method()

使用orm框架转换

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

# 创建对象的基类:
Base = declarative_base()

# 初始化数据库连接:

engine = create_engine("mysql+mysqlconnector://root:baby5429@localhost:3306/db1")

Base.metadata.create_all(engine)


# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    username = Column(String(20))
    password = Column(String(11))

    def __init__(self,id, username, password):
        self.id = id
        self.username = username
        self.password = password


# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)


session = DBSession()

if __name__ == "__main__":

    user1 = User("6","zhansan3","123456")
    session.add(user1)
    session.commit()

概念和数据类型

使用步骤

• 安装
  pip install sqlalchemy该框架是配合之前的两个框架一起使用的。

• 注册数据库驱动

from sqlalchemy import create_engine
 
engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8")

create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'

数据库类型为mysql，数据库驱动为mysql-connecter。
还有额外的配置参数：

engine = create_engine("mysql://user:password@hostname/dbname?charset=uft8",
            echo=True,
            pool_size=8,
            pool_recycle=60*30
            )

echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
pool_recycle: 设置时间以限制数据库多久没连接自动断开

• 创建数据库映射类

from sqlalchemy import Column, String
from sqlalchemy.ext.declarative import declarative_base
# 构造基类
Base = declarative_base()

# 定义User对象
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    username = Column(String(20),unique=False)
    password = Column(String(11))

    def __init__(self,username, password):
        self.username = username
        self.password = password

base.metadata.create_all(engine)

declarative_base()是sqlalchemy内部封装的一个方法，通过其构造一个基类，这个基类和它的子类，可以将Python类和数据库表关联映射起来。base.metadata.create_all(engine)是创建表，在engine定义的数据中创建User表，已有就忽略。数据库表模型类通过```表名````和表关联起来是基本属性，Column表示数据表的列。

如果数据库没有表结构通过base.metadata.create_all(engine)可创建表。执行这段代码，就会发现在db中创建了users表。

• 创建数据库会话

Session 在 sqlalchemy 中用于在程序和数据库之间创建会话。所有对象的加载和保存都需要会话对象。

DBSession = sessionmaker(bind=engine)
session = DBSession()

session的常见操作方法有：

flush：预提交，提交到数据库文件，还未写入数据库文件中
commit：提交了一个事务
rollback：回滚
close：关闭

增加
add()方法添加，参数是类的实例对象

user1 = User("zhansan3","123456")
session.add(user1)
session.commit()

add_all()方法批量添加

查看

user = session.query(User).filter_by(id=1).all()
    for i in user:
        print(i)
        print(i.username)
        print(i.password)

query()方法查询，参数是基类的子类对象即映射的表对象。filter_by()方法是筛选，相当于where。此时还没有去具体的数据库中查询，只有当执行具体的.all()，.first()等函数时才会真的去操作数据库。前者返回所有数据，后者返回第一条数据。

user = session.query(User) 
    for i in user:
        print(i)
        print(i.username)
        print(i.password)

其中，query有filter和filter_by两个过滤方法，上述例子也可写为：

user = session.query(User).filter(User.id == 1).all()

filter_by和filter的区别：

在过滤属性时filter必须用类名.属性，且不支持联合查询。filter_by可以直接用属性，支持联合查询。

# filter_by
users = session.query(Users).filter_by(id=1).all()
''' query绑定对象后filter_by直接过滤属性即可，直接用=，!=，>，<。 '''


# filter
user = session.query(User).filter(User.id == 1).all()
''' 即使通过query绑定仍然要用类名.属性，相等用 == 不支持联合查询 '''

有filter和filter_by的语句在.all()或.first()后才会去操作数据库

改变
更新数据有两种方法，一种是使用query中的update方法：

session.query(User).filter_by(id=8).update({
    'password': "qwerty"})
session.commit()

# update参数是一个字典类型

update的参数是一个字典类型。

另一种是操作对应的表模型，覆盖原来的数据：

user = session.query(User).filter_by(id=8).first()
user.username = "lihua"
session.add(user)
session.commit()

add()方法会覆盖原有数据也达到修改目的。

删除
在mysql中删除数据一般通过主键的字段将一整行删除delete from table where id =?，在SQLAlchemy中则是根据信息查出某一行，让后删掉：

user = session.query(User).filter(User.id == 8).first()
session.delete(user)
session.commit()

另一种是使用查询函数直接删除查询结果：

session.query(User).filter(User.id == 7).delete()
session.commit()

使用这种方法不能加.first或.all，否则变成了返回结果。使用.delete()直接实现对数据库的操作。

https://www.jb51.net/article/173950.htm