python如何操作mysql

工具：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()