python3 连接mysql的模块mysql-connector-python，纯python驱动器，不再依赖C语言的库，并且兼容python2和python3.
说明
本文的代码允许环境 python3.6
安装
pip install mysql-connector-python
连接数据库
#coding:utf-8 import mysql.connector import time try: #连接数据库 con=mysql.connector.connect(host='localhost',port=3306,user='root', password='root',database='test',charset='utf8') print(con.connection_id) time.sleep(5) #断开 con.close() except mysql.connector.Error as e: print(e)
新增操作
一、通过字符串新增
#coding:utf-8 from mysql import connector #连接 try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 # con=mysql.connector.connect(host='localhost',port=3306,user='root', # password='root',database='test',charset='utf8') con=connector.connect(**config) cursor=con.cursor() #通过字符串直接插入 insert1=("insert into user(name,age) values('Tom',20)") cursor.execute(insert1) #提交 con.commit() # 自增id print(cursor.lastrowid) #关闭 cursor.close() con.close() except connector.Error as e: print(e)
二、通过元祖新增
#coding:utf-8 from mysql import connector #连接 try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 # con=mysql.connector.connect(host='localhost',port=3306,user='root', # password='root',database='test',charset='utf8') con=connector.connect(**config) cursor=con.cursor() #通过tuple方式插入（利用%s作为占位符） insert2=("insert into user(name,age) values(%s,%s)") data=('Tom',20) cursor.execute(insert2,data) #提交 con.commit() # 自增id print(cursor.lastrowid) #关闭 cursor.close() con.close() except connector.Error as e: print(e)
三、通过字典新增
#coding:utf-8 from mysql import connector #连接 try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 # con=mysql.connector.connect(host='localhost',port=3306,user='root', # password='root',database='test',charset='utf8') con=connector.connect(**config) cursor=con.cursor() #通过dict方式插入（利用%(字段)s作为占位符） insert3=("insert into user(name,age) values(%(name)s,%(age)s)") data={ 'name':'Tom', 'age':21 } cursor.execute(insert3,data) #提交 con.commit() # 自增id print(cursor.lastrowid) #关闭 cursor.close() con.close() except connector.Error as e: print(e)
四、批量新增
这是以元祖新增为例子，也可以用字典的方式的哦
#coding:utf-8 from mysql import connector #连接 try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 # con=mysql.connector.connect(host='localhost',port=3306,user='root', # password='root',database='test',charset='utf8') con=connector.connect(**config) cursor=con.cursor() #批量插入 insertmany=("insert into user(name,age) values(%s,%s)") data=[ ('Tom1',20), ('Tom2',21), ('Tom3',22) ] cursor.executemany(insertmany,data) #提交 con.commit() #关闭 cursor.close() con.close() except connector.Error as e: print(e)
删除操作
与新增语法相似
#coding:utf-8 from mysql import connector try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 con=connector.connect(**config) cursor=con.cursor() #直接通过字符串方式 delete1=("delete from user where name='Tom'") cursor.execute(delete1) #通过tuple方式 delete2=("delete from user where name=%s and age=%s") data=('Tom',20) cursor.execute(delete2,data) #通过dict方式 delete3=("delete from user where name=%(name)s and age=%(age)s") data={ 'name':'Tom', 'age':20 } cursor.execute(delete3,data) #提交 con.commit() #关闭 cursor.close() con.close except connector.Error as e: print(e)
更新操作
语法相似，字符串、元祖、字典的方式
#coding:utf-8 from mysql import connector try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接数据库 con=connector.connect(**config) cursor=con.cursor() #通过字符串方式直接更新 update1=("update user set name='Tom1',age=20 where Id=81") cursor.execute(update1) #通过tuple方式 update2=("update user set name=%s,age=%s where Id=%s") data=('Tom2',21,81) cursor.execute(update2,data) #通过dict方式 update3=("update user set name=%(name)s,age=%(age)s where Id=%(Id)s") data={ 'name':'Tom3', 'age':29, 'Id':81 } cursor.execute(update3,data) #提交 con.commit() #关闭 cursor.close() con.close() except connector.Error as e: print(e)
查询操作
也是三种查询方式
#coding:utf-8 from mysql import connector try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接 con=connector.connect(**config) cursor=con.cursor() #利用字符串方式查询 query1=("select Id,name,age from user where Id>10") cursor.execute(query1) #取出字段名称集合 columns=cursor.column_names #取出全部数据 result=cursor.fetchall() print '数据表字段名称：{0}'.format(columns) print '查询结果：{0}'.format(result) #关闭 cursor.close() con.close() except connector.Error as e: print(e)
上面的例子返回的是元祖的方式，如果需要返回字典，需要加上这段cursor=con.cursor(cursor_class=connector.cursor.MySQLCursorDict)
例如：
#coding:utf-8 from mysql import connector __author__ = 'kikay' try: #配置信息 config={ 'host':'localhost', 'port':3306, 'user':'root', 'password':'root', 'database':'test', 'charset':'utf8' } #连接 con=connector.connect(**config) cursor=con.cursor(cursor_class=connector.cursor.MySQLCursorDict) #利用字符串方式查询 query1=("select Id,name,age from user where Id>10") cursor.execute(query1) #取出字段名称集合 columns=cursor.column_names #取出全部数据 result=cursor.fetchall() print '数据表字段名称：{0}'.format(columns) print '查询结果：{0}'.format(result) #关闭 cursor.close() con.close() except connector.Error as e: print(e)
引用
https://dev.mysql.com/doc/connector-python/en/
pypi: https://pypi.org/project/mysql-connector-python/#description
https://blog.csdn.net/kikaylee/article/details/53569058