编程►python

python访问mysql(二)

关于mysql中的事务、python访问mysql简单步骤、mysql常见操作等内容点击python访问mysql查看。
在前面介绍python访问mysql的一个典型执行过程中出现了cursor()方法，它就是游标。我们执行sql命令或者得到执行结果都需要使用游标。

curosr()

cursor游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以将游标当做一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

建立数据库连接之后，使用cursor提供的方法执行命令或者返回结果。

执行命令

  • execute(self, query, args=None)
    执行一个数据库sql命令。
  • executemany(self, query, args)
    执行多个数据库sql命令。
  • callproc(self, procname, args=())
    执行存储过程。

上述方法返回结果为受影响的行数。

返回结果

  • fetchall(self)
    接受全部的返回结果行。
  • fetchmany(self, size=None)
    接受size条返回结果行。如果size值大于结果行的数量则返回cursor.arraysize条数据。
  • fetchone(self)
    返回结果行的下一行。

上述方法返回结果为元组tuple。
示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
import pymysql

def conn_mysql():
    db_config={
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '123456',
        'db': 'mysql'
    }
    conn = pymysql.connect(**db_config)
    return conn

cnm = conn_mysql()
cus = cnm.cursor()
sql = 'select host,user from user'
try:
    cus.execute(sql)
    print(cus.fetchmany(size=1))    #输出结果：(('localhost', 'mysql.session'),)
    print(cus.fetchall())    #输出结果：(('localhost', 'mysql.sys'), ('localhost', 'root'))
    print(cus.fetchone())    #输出结果：None
    cus.close()    #关闭游标对象
    cnm.commit()    #正确则提交
except Exception as e:
    cnm.rollback()    #错误则回滚
    print("错误")
finally:
    cnm.close()    #数据库连接需要关闭

说明：
执行该sql最后结果为3条，fetchall()、fetchmany()和fetchone()出现位置不同，则结果不同。
fetchmany(size=1) 取出结果集中的第一条。此时游标处于第二行开始位置。
fetchall() 取出结果集中的全部，也就是游标当前位置至结束，此时游标处于结果集末尾。
fetchone() 取出结果集中的下一条，此时游标处于末尾，返回None。
executemany说明
executemany()主要是针对一条sql需要执行多次，每次执行的参数不同情况。数据量较多时建议使用executemany()方法，速度较快。
下面是executemany()简单示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='test')
cus = conn.cursor()
sql = 'insert into test(id, name) values(%s, %s)'
args = [(1, 'Alice'), (2, 'Bob'), (3, 'Cellary')]
try:
    print(cus.executemany(sql, args))    #输出结果：3
except Exception as e:
    print("执行出错：{0}".format(e))
finally:
    cus.close()
    conn.commit()
    conn.close()

数据库连接池

python编程中使用pymysql进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时都是独立的额请求，比较浪费资源，而且访问数量达到一定数量时，对mysql性能会产生较大的影响。因此在实际使用过程中通常会使用数据库的连接池技术来访问数据库，从而实现资源复用。同时也可以保证数据库不会因为连接数过多而造成数据库宕机。
在python中DBUtils是一套数据库连接池包，提供2种接口。

  • PersistentDB ：提供线程专用的数据库连接，并自动管理连接。
  • PooledDB ：提供线程间可共享的数据库连接，并自动管理连接。

下面使用PooledDB示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymysql
from DBUtils.PooledDB import PooledDB

from DBUtils.PooledDB import PooledDB
db_config = {
        "host": "127.0.0.1",
        "port": 3306,
        "user": "root",
        "passwd": "123456",
        "db": "test",
        # "charset": "utf8"
    }

spool = PooledDB(pymysql, 5, **db_config)  # 5为连接池里的最少连接数
conn = spool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接
cur = conn.cursor()
SQL = "select * from test;"
r = cur.execute(SQL)
r = cur.fetchall()
print(r)
cur.close()
conn.close()

数据库操作

常见的mysql操作温习

1
2
3
4
5
6
7
8
查看所有的库  show databases;
切换数据库  use test
查看库中所有的表  show tables;
查看数据库连接数  show processlist
授权超级用户  grant all privileges on *.* to 'user'@'%' identified by 'password' with grant option;
查询建表语句 show create table student\G
查询表结构 desc student;
查看index  show index from student;

\G可以使结果显示更容易查看。
数据库操作其实就是SQL语句，介绍常用的增删改查语句。

  • 建表create
    1
    2
    3
    4
    5
    6
    7
    create table student(
        StdID int not null,
        StdName varchar(100),
        Gender enum('M','F'),
        Age int,
        Score int
    )

mysql中常用数据类型为int(整型)和varchar(字符型)，当字段取值为固定值时，可以采用枚举类型enum。
char和varchar区别在于存储数据时，当数据大小小于声明的大小时，varchar按照实际数据大小存放，char则会添加空格至声明的数据大小。
varchar(5)存储123，实际存储为123。
char(5)存储123，实际存储为123(后面带2空格)。

  • 插入insert
    1
    2
    3
    insert into student(StdID,StdName,Gender,Age,Score)values(01,'xiaohh','M',18,99);
    insert into student(StdID,StdName,Score,Age)values(02,'Bob',59,18);
    insert into student(StdID,StdName,Score)values(03,'Alice',68),(04,'Lisa',88);

最后一条语句为插入多行的写法。

  • 更新update

    1
    update student set Score=100 where StdID=01 and Score=99;

  • 查询select

    1
    2
    3
    select * from student;
    select StdID,Score from student;
    select * from student where Score > 59 and Gender='M';

  • 删除delete

    1
    2
    3
    delete from student where Score < 60;
    truncate table student;
    drop table student;

truncate：清空表中的数据，和不带where条件的delete作用一样。
drop：删除表结构。

  • 创建索引index

    1
    2
    create index idx_std_stID on student(StdID);
    alter table student add index idx_std_stScore(StdID,Score);

  • 删除索引index

    1
    alter table student drop index idx_std_stScore;

通过常见索引可以加快语句查询的速度，类似于字典中的目录。
那么我们如何确定语句是否使用索引呢？使用explain+sql语句即可。
explain select StdID from student;
mysql_explain_StdID
explain select Score from student;
mysql_explain_Score
explain select Age from student;
mysql_explain_Age
从字段key中可以看到索引名称，若为null则表示未使用索引。
备注
对于sql语句及index索引只是介绍基本的语法规则，更多内容以后继续学习。
查询中可能涉及到多表的join，可能会用到分组group by，也可能会使用到更多的count()、order by等函数。
索引可能会涉及到唯一索引UNIQUE INDEX、全文索引FULLTEXT INDEX等内容。

Recommended Posts