python中mysql的用法_Python中MySQL用法

Python中MySQL用法

一、注意事项查看系统版本：arch命令，查看系统是64位还是32位

使用cat /etc/system-release查看内核版本

注意安装MySQL的版本企业版：付费

社区版：免费

MariaDB

注意安装之后避免yum自动更新版本

注意数据库的安全性

二、字符集设置及mysql命令

(一)、字符集设置字符集分类：ASCII：占用一个字节

LATIN：对ASCII做了扩展

GBK：占用一个或两个字节，windows默认的字符集

utf8：占用3个字节，像emoje等占用四个字节的数据无法存储

UTF-8：占用四个字节，在mysql中写法位utf8mb4

查看字符集：show variables like ‘%character%’;mysql> show variables like '%character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+查看校对规则：show variables like ‘collation_%’;mysql> show variables like 'collation_%';

+----------------------+--------------------+

| Variable_name | Value |

+----------------------+--------------------+

| collation_connection | utf8mb4_0900_ai_ci |

| collation_database | utf8mb4_0900_ai_ci |

| collation_server | utf8mb4_0900_ai_ci |

+----------------------+--------------------+

3 rows in set (0.01 sec)_ci表示大小写不敏感，_cs表示大小写敏感配置文件修改mysql字符集命令：vim /etc/my.cnf[client-server]

default_character_set = utf8mb4

[mysql]

default_character_set = utf8mb4

#interactive_timeout = 28800 #针对交互连接的超时时间

#wait_timeout = 28800 #针对非交互连接的超时时间

#max_connections = 1000 #MySQL的最大连接数

#character_set_server = utf8mb4 #MySQL的字符集设置

#init_connect = 'SET NAMES utf8mb4' # 服务器为每个连接的客户端执行的字符串

#character_set_client_handshake = False

#collation_server = utf8mb4_unicode_ci

(二)、mysql常用命令启动服务：systemctl start mysqld.service

查看服务状态：systemctl status mysqld.service

关闭服务：systemctl stop mysqld.service

重启服务：systemctl restart mysqld.service

登陆mysql：mysql -u 用户 -p 密码

更改用户密码：ALTER USER ‘用户名’@‘localhost’ IDENTIFIED BY ‘新密码’

三、Python连接MySQL的API概念：Python语言：Python Database API 或者DB-API

其他语言：连接器、绑定、binding

分类：MySQLdb：Python2的包，适用于MySQL5.5和Python2.7

Python3连接MySQL：mysqlclient:Python3安装MySQLdb的包叫做mysqlclient，但加载的依然是MySQLdb

安装：pip3 install mysqlclient

导入：import MySQLdb

pymysql：pip install pymysql (流行度最高)

Mysql-connector-python：pip install mysql-connector-python (MySQL官方)，推荐使用

使用ORM(对原始的DB-API的扩展)：sqlalchemypip install sqlalchemy

Django框架Model模型

四、pymysql操作

(一)、查询数据导入pymysql：import pymysql

使用pymysql中的connect方法创建连接对象：参数如下host

user

password

db注意：参数都是关键字参数，如果不是关键字参数，会报如下错误：TypeError: __init__() takes 1 positional argument but 5 were given通过调用连接创建游标对象cursor

调用cursor.execute方法执行sql语句

cursor.fetchone()获取一条数据，fetchall()获取多条数据#!/usr/bin/env python

import pymysql

# 打开数据库连接

db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")

try:

# 使用cursor方法创建一个游标对象

with db.cursor() as cursor:

sql = 'SELECT * FROM book'

cursor.execute(sql)

books = cursor.fetchall()

for book in books:

print(book)

db.commit()

except Exception as e:

print(f'fetch error {e}')

finally:

db.close()

print(f'Database version : { result }')

(二)、插入数据插入单行数据：value = (数据字读内容)，执行方法是：execute(sql, value)

插入多行数据：values = ((数据字段内容1), (数据字段内容2), (数据字段内容3)……)，执行方法是：executemany(sql, values)#!/usr/bin/env python

import pymysql

# 打开数据库连接

db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")

try:

# 使用cursor方法创建一个游标对象

with db.cursor() as cursor:

sql = '''INSERT INTO book (id, name, price) VALUES (%s, %s, %s)''' # 全部用s，无论字段类型

value = (1, "平凡的世界", 23.0) # 传入的值

cursor.execute(sql, value)

db.commit() # 在pymysql中必须使用commit()

except Exception as e:

print(f'fetch error {e}')

finally:

db.close()

print(cursor.rowcount) # rowcount写入的数据的行数(作用的行数，不是表中的总行数)

(三)、更新数据#!/usr/bin/env python

import pymysql

# 打开数据库连接

db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")

try:

# 使用cursor方法创建一个游标对象

with db.cursor() as cursor:

sql = '''UPDATE book SET name = %s WHERE id = %s'''

value = ("巴黎圣母院", 1)

cursor.execute(sql, value)

db.commit()

except Exception as e:

print(f'fetch error {e}')

finally:

db.close()

print(cursor.rowcount)

(四)、删除数据#!/usr/bin/env python

import pymysql

# 打开数据库连接

db = pymysql.connect(host="47.98.222.6", user="liquanhui01", password="liqh930215", db="testdb")

try:

# 使用cursor方法创建一个游标对象

with db.cursor() as cursor:

sql = '''DELETE FROM book WHERE name = %s'''

value = ("巴黎圣母院")

cursor.execute(sql, value)

db.commit()

except Exception as e:

print(f'fetch error {e}')

finally:

db.close()

print(cursor.rowcount)

(五)、pymysql.connect(参数)参数的传入方式方式一：直接使用关键字参数，传入对应的内容

方式二：把参数写入一个配置文件中，创建方法读取配置文件中的对应内容，返回字典格式，再把字典格式的数据作为参数传入配置文件# config.ini文件

[mysql]

host = 47.98.222.6

user = liquanhui01

password = liqh930215

database = testdb

# 注意：init文件中key对应的值不能添加引号# dbconfig.py文件，该文件中的方法用于读取并返回字典格式的配置参数

from configparser import ConfigParser

from pathlib import Path

p = Path(__file__)

cur_path = p.resolve().parent

file_path = cur_path.joinpath("config.ini")

def read_db_config(filename=file_path, section="mysql"):

# section规定传入哪一部分的内容

# 实例化配置文件解析类，调用该类的read方法获取section对应的文件内容

parser = ConfigParser()

parser.read(filename)

if parser.has_section(section):

items = parser.items(section)

print(items)

else:

raise Exception('{0} not found in the {1} file'.format(section, filename))

return dict(items)

if __name__ == "__amin__":

read_db_config()

# pymysql_cnn.py

#!/usr/bin/env python

import pymysql

from example.dbconfig import read_db_config

dbserver = read_db_config()

print(dbserver)

# 打开数据库连接

db = pymysql.connect(**dbserver)

.....省略

五、sqlalchemy操作

(一)、sqlalchemy core的方式创建数据表导入pymysql

从sqlalchemy中导入create_engine，Table, Column以及其他的字段信息

使用create_engine创建引擎，方法内部为字符串。格式为：engine = create_engine("mysql+pymysql://用户名:密码@域名:端口/数据库", echo=True)

# echo=True开启终端打印模式，在生产模式下需要关闭创建元数据：元数据是对数据库的描述信息，metadata = MetaData(engine)

创建表：变量名 = Table(‘表名’, metadata, 字段信息)

执行metadata.create_all()执行创建表的命令，注意设置异常处理#!/usr/bin/env python

import pymysql

from sqlalchemy import create_engine, Table, Column, Integer, String, ForeignKey, Float, MetaData, DateTime

# 创建引擎

engine = create_engine("mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb", echo=True)

# 创建元数据

metadata = MetaData(engine)

# 创建表

book_table = Table('book', metadata,

Column('id', Integer, primary_key=True),

Column('name', String(100), nullable=False),

Column('desc', String(255), nullable=True),

Column('price', Float, nullable=False)

)

author_table = Table('author', metadata,

Column('id', Integer, primary_key=True),

Column('name', String(30), nullable=False),

Column('book_id', ForeignKey('book.id'), None)

)

try:

metadata.create_all()

except Exception as e:

print(f'create error {e}')

(二)、sqlalchemy orm的方式创建表使用orm方式的四个前提条件：Base必须继承自declarative_base()

使用类创建数据表的时候必须使用属性__tablename__=“表名”的方式设置表名, tablename是双下方法，md文件自动隐藏了下滑线

必须包含一个或者多个Column字段(属性)

必须包含一个主键

导入模块import pymysql

from sqlalchemy import create_engine, Table, Column,字段类型

from sqlalchemy.ext.declarative import declarative_base

创建Base：Base = declarative_base()

创建类，继承自Base，在类中添加__basename__和其他Column属性

创建dbUrl，格式如下：dbUrl = "mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb?charset=utf8mb4"创建引擎：engine = create_engine(dbUrl, echo=True, encoding=“uff-8”)

创建数据表：Base.metadata.create_all(engine)#!/usr/bin/env python

import pymysql

from sqlalchemy import create_engine, Table, Column, Integer, String, Float, DateTime, ForeignKey

from sqlalchemy.ext.declarative import declarative_base

from datetime import datetime

Base = declarative_base()

class Category(Base):

__tablename__ = "category_rom"

id = Column(Integer(), primary_key=True, autoincrement=True)

category_name = Column(String(50), index=True)

class Products(Base):

__tablename__ = "product_rom"

id = Column(Integer(), primary_key=True, autoincrement=True)

product_name = Column(String(50), index=True)

category = Column(Integer, ForeignKey('category_rom.id', ondelete="SET NULL"))

create_on = Column(DateTime(), default=datetime.now)

update_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

dburl = "mysql+pymysql://liquanhui01:liqh930215@47.98.222.6:3306/testdb?charset=utf8mb4"

engine = create_engine(dburl, echo=True, encoding="utf-8")

Base.metadata.create_all(engine)

(三)、增删改查操作创建session对象增删改查操作都是基于session进行的

导入：from sqlalchemy.orm import sessionmaker

创建session:# 创建session

from sqlalchemy.orm import sessionmaker

SessionClass = sessionmaker(bind=engine)

session = SessionClass()增加操作class_demo = ClassName(field=“值”, ...)

session.add(class_demo)

session.commit()# 创建session

SessionClass = sessionmaker(bind=engine)

session = SessionClass()

# 添加教师数据

teacher_demo = Teacher(name="李贞贞")

session.add(teacher_demo)

# 添加学生数据

students = ["黎明", "麻织", "杨志", "冉阿让"]

for student in students:

student_demo = Students(name=student)

session.add(student_demo)

session.commit()查询操作获取数据集：查询数据表中的全部字段：使用query = session.query(ClassName)获取数据集，相当于select *

查询一部分字段：使用query = session.query(ClassName.字段名, ClassName.字段名, ...)

使用聚合函数获取查询集：导入func：from sqlalchemy import func

query = session(func.聚合函数名(ClassName.字段名))

对查询的数据做排序操作：升序：query.order_by(ClassName.字段名)

降序：导入desc方法：from sqlalchemy import desc

query.order_by(desc(ClassName.字段名))

对查询的数据集做过滤操作直接写字段条件：query.filter(ClassName.字段+条件, ClassName.字段+条件, …)

使用与、或、非导入：from sqlalchemy import and_, or_, not_, 注意and、or和not后都有一个下滑线，md文件隐藏了

query.filter(or_/and_/not_(ClassName.字段名+条件, ClassName.字段名+条件, ClassName.字段名+条件, ClassName.字段名+条件….))

获取全部数据：query.all()，再通过循环取出

获取单个数据：query.first()获取，或者通过query.one()、query.scale()获取。推荐使用first()

session.commit()data = session.query(Teacher).all()

data = session.query(Students).first()

for result in session.query(Students):

print(result.all())

data = session.query(Students.id, Students.name).order_by(desc(Students.id))

for student in data:

print(student)

query = session.query(Students.id, Students.name).order_by(desc(Students.id)).limit(3)

print([student.name for student in query])

query = session.query(func.count(Students.name)).first()

query = session.query(Students).filter(Students.id > 2, Students.id < 5)

print([[student.id, student.name] for student in query])

query = session.query(Students).filter(or_(

Students.id.between(2, 4),

Students.name.contains("黎")

))

print([student.name for student in query])

session.commit更新数据获取指定数据的数据集：query = session.query(ClassName).filter(ClassName.字段 == 值)

更新数据： query.update({ClassName.字段名: value, ...})

session.commit()query = session.query(Students).filter(Students.id == 3)

query.update({Students.name: "管仲"})

print(query.first())

session.commit()删除数据获取指定数据的数据集：query = session.query(ClassName).filter(ClassName.字段 == 值)

删除数据：方式一：session.delete(query.one())

方式二：query.delete()

session.commit()

六、SQL基础知识select查询时关键字的书写顺序：SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT需要注意的是：生产环境下因为列数很多，一般禁用SELECT *

WHERE字段为避免全表扫描，一般需要增加索引select查询时的执行顺序：SELECT DISTINCT player_id, player_name, count(*) as num -- 第五步：从虚拟表中查询出player_id, player_name, count(*)数据

FROM player JOIN team ON player.team_id = team.team_id -- 第一步：从两个表中查询出player.team_id = team.team_id的数据生成一张新的虚拟表

WHERE height > 1.80 -- 第二步：从虚拟表中查询出height>1.80的数据生成新的虚拟表

GROUP BY player.team_id -- 第三步：以player.team_id为条件进行分组

HAVING num > 2 -- 第四步：筛选num > 2的数据

ORDER BY num DESC -- 第六步：根据num的值进行降序排列，升序是asc，降序是desc

LIMIT 2 -- 第七步：取出前两条数据，查询步骤结束

/*

WHERE作用于字段，即每一行数据

HAVING作用于GROUP BY，所有在GROUP BY分组之后如果还要再进行筛选，必须使用HAVING，不能使用WHERE

*/SQL函数：算数函数

字符串函数

日期函数

转换函数

聚合函数COUNT() 行数

MAX() 最大值

MIN() 最小值

SUM() 求和

AVG() 平均值注意：聚合函数忽略空行

子查询概念：需要从查询结果中集中再次进行查询，才可以得到想要的结果，一次无法得到结果

分类关联子查询：复杂，嵌套查询，需要使用EXIST或IN关键字当内层查询表为小表，外层查询表为大表时使用IN关键字SELECT * FROM TABLE_A WHERE condition IN (SELECT condition FROM TABLE_B)

-- A为大表，B为小表，以小表作为优先限制条件可以减少查询的数据量，提升查询的效率- 当内层查询表为大表，外层查询表为小表时使用EXIST关键字

```sql

SELECT * FROM TABLE_A WHERE EXIST (SELECT condition FROM TABLE_B WHERE B.condition = A.condition)

-- B为大表，A为小表，以小表作为优先限制条件可以减少查询的数据量，提升查询的效率

```

- 非关联子查询：简单，内层的语句只执行一次

```sql

SELECT COUNT(*), n_star FROM t1 GROUP BY n_star HAVING n_star > (SEECT avg(n_star) FROM t1) ORDER BY n_star DESC;

```常用的连接(JOIN)自然连接：inner join内部连接，获取两个表的公共部分

ON连接

USING连接

外连接左外连接：leftjoin—>只获取左表中两个表的公共部分数据

右外连接：right join —>只获取右表中两个表的公共部分数据

全外连接(MySQL不支持)：获取两个表中的全部数据，由于在MySQL中的不支持，可以使用union来代替

事务执行同步，要么全执行，要么不执行

事务的特性 —— ACIDA：原子性(Atomicity)

C：一致性(Consistency)

I：隔离性(Idolation)

D：持久性(Durability)

事务的隔离级别读未提交：允许读到未提交的数据，级别最低，无法用在高并发场景

读已提交：只能读到已经提交的数据

可重复性：同一事务在相同的查询条件下两次查询的结果一致， 默认事务级别

可串行化：事务进行串行化，排队执行，牺牲了并发性能，级别最高> MySQL中默认自动提交查询自动提交设置：show variables like “autocommit”;

关闭与开启自动提交：关闭：set autocommit = 0

开启：set autocommit = 1

开启事务：BEGIN

提交：COMMIT

回滚：RALLBACK

回滚至某一个保存点：ROLLBACK TO