python3 mysql模块mysql-connector-python使用方法
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)