PYTHON 访问MySQL数据库
要使用MySQL数据库,需先安装MySQL服务器。在服务器中创建数据库,然后通过客户端程序访问数据库。
本节主要讲述:
–下载安装MySQL
–访问MySQL数据库实例
–连接MySQL服务器
–MySQL数据库操作
–MySQL表操作
–MySQL查询参数
–使用存储过程
–使用事务
下载安装MySQL
•可从MySQL官方网站下载社区版,下载地址为:http://dev.mysql.com/downloads/mysql/
访问MySQL数据库实例
下面代码先创建一个MySQL数据库plandb,在数据库中创建一个表data,然后将7.2.9节中使用的plan.csv文件中的数据导入表data中。通过该实例,了解访问MySQL数据库的基本过程。
连接MySQL服务器
•使用Python访问MySQL服务器或数据库时,都需要先建立连接,即创建Python的SQL接口的连接对象,所有数据库操作均通过连接完成。
1.建立连接
•MySQL连接器提供了两种方法来建立连接。
–调用mysql.connector.connect()函数。
–调用mysql.connector.MySQLConnection()类。
•例如:
>>> import mysql.connector
>>> cn1=mysql.connector.connect(host='localhost',
user='root',password='123456')
>>> cn2=mysql.connector.MySQLConnection(user='root',
password='123456',db='plandb')
2.关于当前数据库
•如果在连接参数中指定了连接的数据库,则访问数据库对象(表、视图、存储过程等)时,默认为当前数据库的对象。要访问其他数据库的对象,则需要使用“数据库名.”作为限定词。例如,访问plandb数据库的data表时,可使用plandb.data。
•MySQL连接器允许通过当前连接使用多个数据库,但只有一个当前数据库。可使用连接对象的database属性查看或设置当前数据库。
>>> cn1.database
>>> cn1.database='mysql'
3.测试连接是否可用
•可使用连接对象的ping()或is_connected()方法来测试连接是否仍然可用。
–ping()方法在连接可用时返回空值,连接不可用时发生InterfaceError异常。
–is_connected()方法:在连接可用时返回True,连接不可用时返回False。
4.修改和重建连接
•可通过连接对象修改连接参数,修改连接参数后需重建连接使修改生效。
–config()方法:在建立连接后,用该方法修改连接参数配置,修改后,用reconnect()方法重建连接。
–cmd_change_user()方法:可改变连接使用的用户名、密码、数据库和字符集。
5.关闭连接
•访问完MySQL后,可使用下面的方法关闭连接。
–close()方法:向MySQL服务器发送QUIT语句,关闭当前连接。
–disconnect()方法:与close()方法相同。
–shutdown()方法:不发送QUIT,直接关闭当前连接。
MySQL数据库操作
•数据库基本操作包括:
–创建数据库
–修改数据库
–删除数据库
1.创建数据库
•MySQL使用create语句来创建数据库,其基本格式为:
–create{database | schema} [ if not exists]
db_name[options]
•例如:
–createdatabase test #最简单的创建数据库命令
–createdatabase if not exists test2 #避免创建同名数据库
2.修改数据库
•可修改数据库的字符集或排序规则。修改数据库语句基本格式如下。
–alter{database| schema} character
set 字符集名称collate排序规则
•例如:
–>>>cn.cmd_query('create database test')
–>>>cn.cmd_query('alter database test character set
gbkcollate
gbk_chinese_ci')
3.删除数据库
•删除数据库名称的语句基本格式如下。
–drop{database| schema}db_name
•例如:
–>>>cn.cmd_query('drop database test')
MySQL表操作
•建立连接后,MySQL数据库中的表操作和SQLite数据库中的表操作类似。在下面的代码中,分别使用连接对象的cmd_query()方法操作表(创建表、修改表、添加记录、删除记录和执行查询等)
•各种表操作可以自行在IDLE中测试
•在Python中执行MySQL查询时,使用Python风格的查询参数。可使用连接器的paramstyle属性查看参数格式。例如:
•pyformat表示参数采用Python风格,即在select查询字符串中用“%s”表示参数,实际参数按先后顺序放在元组中。
使用存储过程
•存储过程是存储在服务器中的SQL语句集合,通过存储过程名称来访问,返回执行结果。下面的代码执行create procedure语句创建存储过程。
–>>>cur.execute('create procedure pp(n
int) begin select n*n;select* from data where age > n; end ')
•存储过程名称为pp,参数n为int类型。begin…end之间为存储过程代码,包含了两个select语句。
•在Python中,使用游标对象的callproc()方法来执行存储过程。例如:
>>> cur.callproc('pp',(20,))
(20,)
•执行存储过程时,返回传入的参数。存储过程中的返回数据,如查询结果等,则需使用游标对象的stored_results()方法来获取。stored_results()方法返回一个可迭代对象,迭代对象包含的每个元素为一个查询结集。
•可以通过传出参数返回存储过程中的数据。例如:
>>> cur.execute('createprocedure doadd(in a int,in b int,out n int) beginset n := a + b; end ;')
>>> cur.callproc('doadd',(2,3,0))
(2, 3, 5)
•存储过程doadd的参数a和b为传入参数,out为传出参数。执行存储过程时,a和b分别接收输入参数2和3。参数元组中的0用于为输出参数占位。callproc()返回的元组包含了传入参数和传出参数。
使用事务
•一个事务包含了一系列操作。事务最大的特点为原子性,即事务中的所有操作要么全部成功执行,要么全部无效。当事务中的某个操作失败时,会导致事务回滚,即撤销已执行的操作。
•在MySQL中,与事务有关的语句如下。
–starttransaction或begin:开始一个事务。
–commit:语句之前的所有操作作为事务提交,使操作生效。
–rollback:回滚当前事务,撤销之前以执行的操作
•有些操作是不能撤销的,如创建/删除数据库、创建/删除/修改表等各种数据定义语言(DDL)语句。下面的代码在系统命令行执行mysql命令使用事务。
•在Python中访问MySQL服务器时,连接参数autocommit默认为False,即不会自动提交事务。在执行各种记录修改操作时,会自动开始一个事务,如果不执行commit()方法提交事务,中断连接后,所有修改操作均会失效。
•在MySQL连接器中,连接对象的下列方法与事务相关。
–start_transaction()方法:开始一个事务,也可向服务器发送一个BEGIN或START
TRANSACTION语句来开始一个事物。
–rollback():回滚事务。
–commit()方法:提交当前事务。