Python mysql

发布时间：2019-08-29 07:38:36编辑：auto阅读（1506）

  要想使python可以操作mysql 就需要MySQL-python驱动，它是python 操作mysql必不可少的模块。下载地址：https://pypi.python.org/pypi/MySQL-python/，可以根据不同的平台下载不同的版本。这里以Windows平台为例，资源地址是：https://pypi.python.org/packages/27/06/596ae3afeefc0cda5840036c42920222cb8136c101ec0f453f2e36df12a0/MySQL-python-1.2.5.win32-py2.7.exe#md5=6f43f42516ea26e79cfb100af69a925e；Linux平台需下载源码zip包，下载MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录:

>>python setup.py install

安装完成后，通过cmd验证：

Python 2.7.5 (default, Nov 20 2017, 22:43:36) [MSC v.1500 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> help()
help> MySQLdb
Help on package MySQLdb:

NAME
    MySQLdb - MySQLdb - A DB API v2.0 compatible interface to MySQL.
>>> import MySQLdb

  没有报错提示MySQLdb模块找不到，说明安装OK ，下面开始使用python 操作数据库之前，我们有必要来回顾一下mysql的基本操作：

mysql -u root -p  （有密码时）

mysql -u root     （无密码时）

// 查看当前所有的数据库

mysql> show databases; 

//作用与test数据库或切换数据库

mysql> use test;  

//查看test库下面的表

mysql> show tables; 

//创建user表，name 和password 两个字段

mysql> create table user (name VARCHAR(20),password VARCHAR(20)); 

//向user表内插入若干条数据

mysql> insert into user values('Tom','1321');

//查看user表的数据

mysql> select * from user;

//删除name 等于Jack的数据

mysql> delete from user where name = 'Jack';

//修改name等于Alen 的password 为 1111

mysql> update user set password='1111' where name = 'Alen';

接下来就来举例看下python是如何操作mysql的：

import MySQLdb
conn= MySQLdb.connect(
        host='localhost',
        port = 3306,
        user='root',
        passwd='123456',
        db ='test',
        )
cur = conn.cursor()
#创建数据表
#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
#插入一条数据
#cur.execute("insert into student values('2','Tom','3 year 2 class','9')")
#一次插入多条记录
sqli="insert into student values(%s,%s,%s,%s)"
cur.executemany(sqli,[
    ('3','Tom','1 year 1 class','6'),
    ('3','Jack','2 year 1 class','7'),
    ('3','Yaheng','2 year 2 class','7'),
    ])
#修改查询条件的数据
#cur.execute("update student set class='3 year 1 class' where name = 'Tom'")
#删除查询条件的数据
#cur.execute("delete from student where age='9'")
#获取表中数据
cur.fetchone()
#获取第一条数据
cur.scroll(0,'absolute') 
#打印表中的多少数据
info = cur.fetchmany(aa)
for ii in info:    
   print ii
cur.close()
conn.commit()
conn.close()

上面的例子中用到了游标对象cursor和事务execute，它们各自支持的方法有：

execute(op[,args])	执行一个数据库的查询和命令
fetchone()	获取结果集的下一行
fetchmany(size)	获取结果集的下几行务
fetchall()	获取结果集中剩下的所有行
rowcount()	最近一次execute返回数据的行数或影响的行数
close()	关闭游标对象

execute操作的集合称为事务。 

事务：访问和更新数据库的一个程序执行单元。

原子性：事务中包括的所有操作只有都做和都不做连个选择。

一致性：事务必须使数据库从一致性状态变到另一个一致性状态。

隔离性：一个事务的执行不能被其他事务干扰。

持久性：一旦事务提交了，它对数据库的改变就是永久性的。

使用事务的方法：

关闭自动commit：设置conn.autocommit(False)

正常结束事务：conn.commit()

异常结束事务：conn.rollback() 

注意不关闭自动更新事务，每一条sql语句都是会更新事务，假如有一条执行错误，那就GG了。默认关闭。

conn = MySQLdb.connect(host='localhost',port = 3306,user='root', passwd='123456',db ='test')

connect() 方法用于创建数据库的连接，里面可以指定参数：用户名，密码，主机等信息。这只是连接到了数据库，要想操作数据库需要创建游标。

cur = conn.cursor()

通过获取到的数据库连接conn下的cursor()方法来创建游标。

cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")

通过游标cur 操作execute()方法可以写入纯sql语句。通过execute()方法中写如sql语句来对数据进行操作。

cur.close()     关闭游标

conn.commit()  方法在提交事物，在向数据库插入一条数据时必须要有这个方法，否则数据不会被真正的插入。

conn.close()  关闭数据库连接

executemany()  方法可以一次插入多条值，执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数。


fetchone()  方法可以帮助我们获得表中的数据，可是每次执行cur.fetchone() 获得的数据都不一样，换句话说我没执行一次，游标会从表中的第一条数据移动到下一条数据的位置，所以，我再次执行的时候得到的是第二条数据。

scroll(0,'absolute') 方法可以将游标定位到表中的第一条数据。

fetchmany()方法可以获得多条数据，但需要指定数据的条数，通过一个for循环就可以把多条数据打印出。

import MySQLdb
#使用连接池
from DBUtils.PooledDB import PooledDB
#5为连接池里的最少连接数
pool = PooledDB(MySQLdb,5,host='localhost',user='root',passwd='pwd',db='myDB',port=3306) 
#以后每次需要数据库连接就是用connection（）函数获取连接就好了
conn = pool.connection()  
cur=conn.cursor()
SQL="select * from test"
r=cur.execute(SQL)
r=cur.fetchall()
cur.close()
conn.close()

PooledDB的参数：

1. mincached，最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接；

2. maxcached，最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接；

3. maxconnections，最大的连接数；

4. blocking，当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错；

5. maxshared 当连接数达到这个数，新请求的连接会分享已经分配出去的连接；

使用连接池的好处：

1.在程序创建连接的时候，可以从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度；
2.关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接；

  所谓索引的就是具有（约束和加速查找的一种方式），创建索引的缺点是对数据进行（修改、更新、删除）比较慢！在频繁查找使用的数据进行创建索引；通过设置得索引去查找速度较快。

1、主键索引：

　 特性：加速查找、不能为空、不能重复；

2、普通索引INDEX：

　 特性：加速查找；

3、唯一索引：

　 特性：加速查找、可以为空、不能重复；

    主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

    唯一索引UNIQUE:加速查找+约束（不能重复）

4、联合索引：

　 特征：（多列）联合主键索引、联合唯一索引、联合普通索引；  

    -PRIMARY KEY(id,name):联合主键索引

    -UNIQUE(id,name):联合唯一索引

    -INDEX(id,name):联合普通索引

5、全文索引：

　 特征：对文本的内容进行分词，进行搜索；

#上边是通过索引查找，速度快！
===========================================
mysql> select * from useru where uname='alex23232';
+-------+-----------+---------------+--------+
| id    | uname     | emlia         | gender |
+-------+-----------+---------------+--------+
| 23232 | alex23232 | 23232@.qq.com | 男     |
+-------+-----------+---------------+--------+
1 row in set (0.04 sec)

#上边的就是没有通过索引进行查找，速度较慢！
===========================================
mysql> select * from useru where emlia='23423@.qq.com';
+-------+-----------+---------------+--------+
| id    | uname     | emlia         | gender |
+-------+-----------+---------------+--------+
| 23423 | alex23423 | 23423@.qq.com | 男     |
+-------+-----------+---------------+--------+
1 row in set (0.18 sec)

1 创建索引

- 在创建表时就创建

        create table s1(

        id int,

        name char(6),

        age int,

         email varchar(30),

        index(id)

        );

- 在创建表后创建

         create index name on s1(name);#添加普通索引

         create unique index age on s1(age);#添加唯一索引

         alter table s1 add primary key(id);#添加主键索引

         create index name on s1(id,name);#添加联合普通索引

注：在创表的时候创建只能写在后面单写，因为他不是起约束的作用；

2 删除索引

     drop index id on s1;

     drop index name on s1;

     alter table s1 add primary key(id,name);联合主键索引

     alter table s1 drop primary key;#删除主键索引

3 正确使用索引

     select sql_no_cache * from s1 where email='xxx'; #命中索引，速度很快

     select sql_no_cache * from s1 where email like '%old%'; #无法使用索引，速度依然很慢

注：索引必须是一个明确的值才能体现其查询速度，例如where id=30521,如果是范围操作（大于，小于，between），就是还是需要循环判断，索引就不起作用。

 #范围小的话，索引有用
 mysql> select count(*) from s1 where id>1000 and id < 2000;
 +----------+
 | count(*) |
 +----------+
 |      999 |
 +----------+
 1 row in set (0.00 sec)
 
 #范围大的话，索引没用
 mysql> select count(*) from s1 where id>1000 and id < 300000;
 +----------+
 | count(*) |
 +----------+
 |   298999 |
 +----------+
 1 row in set (0.13 sec)

关键字：