连接MySQL, 需要mysql connector, conntector是一种驱动程序,Python连接MySQL的驱动程序,MySQL官方给出的名称为connector/python,可参考mysql官网:https://dev.mysql.com/downloads/connector/
环境
操作系统:Windows 10
python版本:2.7
mysql版本:5.5.53 MySQL Community Server (GPL)
IDE工具:pycharm 2016.3.2
接下来列举python操作mysql的步骤:
1.下载并安装connector/python
A.下载mysql-connector-python-2.1.6-py2.7-winx64.msi,下载之后,根据提示安装即可
下载地址:https://dev.mysql.com/downloads/connector/python/
2.使用命令行往mysql中添加数据
A.进入数据库命令行操作界面,使用mysql -u USERNAME -p PASSWORD
B.数据库常用操作
show databases; # 显示所有数据库
create database t1; # 创建数据库t1
use database t1; #指定当前操作的数据库为t1
drop database t1; #删除数据库t1
C.表中所有数据如下
3.使用Python中的mysql.connector模块操作MySQL
python代码
import mysql.connector
# mysql1.py
config={'host': '127.0.0.1','user': 'root','password': 'root','port': 3306,'database': 'test','charset': 'utf8'}try:
cnn= mysql.connector.connect(**config)
except mysql.connector.Errorase:
print('connect fails!{}'.format(e))
cursor=cnn.cursor()try:
sql_query= 'select name,age from stu ;'cursor.execute(sql_query)for name, age incursor:
print (name, age)
except mysql.connector.Errorase:
print('query error!{}'.format(e))finally:
cursor.close()
cnn.close()
操作结果
(u'xiaoming', 10)
(u'rose', 18)
(u'jack', 19)
(u'fang', 20)
(u'Liang', 40)
(u'Age', None)
更加规范的操作,代码如下
def select2(sql_cmd, param):""":param sql_cmd sql 命令
:param param 参数""" try:
conn= mysql.connector.connect(**config)
except mysql.connector.Errorase:
print('connect fails!{}'.format(e))
cursor=conn.cursor()try:
cursor.execute(sql_cmd, param)
except mysql.connector.Errorase:
print('connect fails!{}'.format(e))finally:
cursor.close()
conn.close()if __name__ == '__main__':
sql_cmd= "insert into stu (name, age, sex) value (%s, %s, %s)"param= ('yangguo', 28, 'male')
select2(sql_cmd=sql_cmd, param=param) # 将命令和参数分隔开,操作起来更加安全
这里只是使用mysql.connector的入门案例