工具:mysql.connector和MySQLdb,(PyMySQL据说也不错,没试过)
1.mysql.connector介绍(推荐):
是MySQL官方提供的Python连接MySQL数据库的驱动程序
是纯python实现的,由Oracle维护
相较于MySQLdb模块来说,其支持python3,而MySQLdb目前只支持到python2.7版本
2.MySQLdb介绍:
也就是mysql-python
3.Demo
3.1 主函数
#!/usr/bin/python
#coding=utf-8
from mysqlconnectordemo import *
from mysqldbdemo import *
if __name__ == '__main__':
conf = {
"user":"xxx",
"password":"xxx",
"host":"localhost",
"database":"mate_app"}
querysql = "select * from tb_app_user_data where app_name='JWIMATE.exe'"
insertsql = "insert into tb_app_user_data(user_id,app_name) values('test','mysqlconnector.exe')"
mcdemo = mysqlconnectordemo()
mcdemo.connect(conf)#conn
mcdemo.query(conf,querysql)#query
mcdemo.insert(conf,insertsql)#insert
mdb = mysqldbdemo()
mdb.connect()
mdb.query(querysql)#query
mdb.insert(insertsql)#insert
3.2 mysql.connector查询和插入操作
#!/usr/bin/python
#coding=utf-8
import mysql.connector
from mysql.connector import errorcode
class mysqlconnectordemo(object):
def __init__(self):
pass
def connect(self,conf):
try:
conn = mysql.connector.connect(**conf)
print("conn success!")
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
conn.close()
def query(self,conf,sql):
try:
conn = mysql.connector.connect(**conf)
print("conn success!")
cursor = conn.cursor()
try:
cursor.execute(sql)
values = cursor.fetchall()#返回是一个由元组构成的list,每一个元组是一行值
print type(values)
for i in values:
print i
except mysql.connector.Error as err:
print('query datas error!{}'.format(err))
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
conn.close()
def insert(self,conf,sql):
try:
conn = mysql.connector.connect(**conf)
print("conn success!")
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
cursor.close()
except mysql.connector.Error as err:
print('insert datas error!{}'.format(err))
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
conn.close()
3.3 MySQLdb查询和插入操作
#!/usr/bin/python
#coding=utf-8
import MySQLdb
class mysqldbdemo(object):
def __init__(self):
pass
def connect(self):
conn = MySQLdb.connect("host_ip","user","password","database")
conn.close()
def query(self,sql):
conn = MySQLdb.connect("host_ip","user","password","database")
cursor = conn.cursor()
try:
cursor.execute(sql)
values = cursor.fetchall()#返回是一个由元组构成的tuple,每一个元组是一行值
print type(values)
for i in values:
print i
except:
print "Error: unable to fecth data"
conn.close()
def insert(self,sql):
conn = MySQLdb.connect("host_ip","user","password","database")
cursor = conn.cursor()
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
conn.close()