python-连接MySQL数据库

Python连接MySQL数据库操作Python操作MySQL有两个模块可以实现.MySQLdb模块和mysql.connector模块.

MySQLdb模块:# encoding: utf-8#!/usr/bin/pythonimport MySQLdb#步骤一、打开数据库连接db = MySQLdb.connect(“localhost”,”testuser”,”test123″,”TESTDB” )db.select_db(‘test’) #改变数据库

#步骤二、 使用cursor()方法获取操作游标 cursor = db.cursor()

#步骤三、执行操作.执行SQL:插入数据,DDL操作类似

sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \       LAST_NAME, AGE, SEX, INCOME) \       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \       ('Mac', 'Mohan', 20, 'M', 2000)try:   # 执行sql语句   cursor.execute(sql)   # 提交到数据库执行   db.commit()except:   # Rollback in case there is any error   db.rollback()执行SQL:查询使用fetchone返回一条数据sql  = "select version()"cursor.execute(sql)# 使用 fetchone() 方法获取一条数据库。data = cursor.fetchone()执行SQL:查询使用fetchall返回所有数据# SQL 查询语句sql = "SELECT * FROM EMPLOYEE \       WHERE INCOME > '%d'" % (1000)try:   # 执行SQL语句   cursor.execute(sql)   # 获取所有记录列表   results = cursor.fetchall()   for row in results:      fname = row[0]      lname = row[1]      age = row[2]      sex = row[3]      income = row[4]      # 打印结果      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \             (fname, lname, age, sex, income )except:   print "Error: unable to fecth data"

步骤四、 关闭数据库连接db.close()

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++mysql.connector模块:#步骤一、打开数据库连接

import mysql.connectordb = mysql.connector.connect(user='scott', password='tiger',                              host='127.0.0.1',                              database='employees')

或者

import mysql.connectorconfig = {  'user': 'scott',  'password': 'tiger',  'host': '127.0.0.1',  'database': 'employees',  'raise_on_warnings': True,}db = mysql.connector.connect(**config)

#步骤二、 使用cursor()方法获取操作游标 cursor = db.cursor()

#步骤三、执行操作插入数据:

from __future__ import print_functionfrom datetime import date, datetime, timedeltaimport mysql.connectordb = mysql.connector.connect(user='scott', database='employees')cursor = db.cursor()tomorrow = datetime.now().date() + timedelta(days=1)add_employee = ("INSERT INTO employees "               "(first_name, last_name, hire_date, gender, birth_date) "               "VALUES (%s, %s, %s, %s, %s)")add_salary = ("INSERT INTO salaries "              "(emp_no, salary, from_date, to_date) "              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))# Insert new employeecursor.execute(add_employee, data_employee)emp_no = cursor.lastrowid# Insert salary informationdata_salary = {  'emp_no': emp_no,  'salary': 50000,  'from_date': tomorrow,  'to_date': date(9999, 1, 1),}cursor.execute(add_salary, data_salary)# Make sure data is committed to the databasedb.commit()cursor.close()db.close()

查询数据：

import datetimeimport mysql.connectordb = mysql.connector.connect(user='scott', database='employees')cursor = db.cursor()query = ("SELECT first_name, last_name, hire_date FROM employees "         "WHERE hire_date BETWEEN %s AND %s")hire_start = datetime.date(1999, 1, 1)hire_end = datetime.date(1999, 12, 31)cursor.execute(query, (hire_start, hire_end))for (first_name, last_name, hire_date) in cursor:  print("{}, {} was hired on {:%d %b %Y}".format(    last_name, first_name, hire_date))cursor.close()db.close()

步骤四、 关闭数据库连接db.close()

mysql.connector的版本对应表：

Connector/Python Version        MySQL Server VersionsPython VersionsSupport Status for Connector2.05.7,5.6, 5.53.3 and later, 2.7, 2.6Recommended version1.1, 1.25.7, 5.6, 5.5 (5.1, 5.0, 4.1)3.1 and later, 2.7, 2.6Recommended version1.05.7, 5.6, 5.5 (5.1, 5.0, 4.1)3.1 and later, 2.7, 2.6 (2.5, 2.4)Recommended version

MySQLdb代码：

#!/usr/bin/env python""" test python connect to mysql """import MySQLdbtry:        db = MySQLdb.connect('192.168.11.17','milanoo','milanoo','test')except:        print "Connect error"#select a versioncursor = db.cursor()sql = "select version()"cursor.execute(sql)data =  cursor.fetchone()print 'MySQL Version is:%s' % datasql_list = ['drop table  if exists t','create table t (id int,name varchar(255),primary key(id))']db.select_db('test')cursor = db.cursor()for i in sql_list:        try:                cursor.execute(i)        except ProgrammingError:                print "SQL Syntax:%s " % i        print "SQL is excute:%s" % iuser_list = [(1,'andy'),(2,'mandy'),(3,'isadba')]for i in user_list:        id = i[0]        name = i[1]        insert_sql = "insert into t(id,name) values ('%d','%s')" % (id,name)        print insert_sql        try:                cursor.execute(insert_sql)        except:                print "error:%s" % insert_sql                db.rollback()        db.commit()query_sql = 'select id,name from t'cursor.execute(query_sql)result = cursor.fetchall()for row in result:        print "id is:%d   name is:%s" % (row[0],row[1])

mysql.connector代码：

#!/usr/bin/env pythonimport mysql.connector as mysqlconfig = {        'user':'milanoo',        'password':'milanoo',        'host':'192.168.11.17',        'database':'test',        'raise_on_warnings':True,}db = mysql.connect(**config)sql = ['drop table if exists t2','create table t2 (id int,name varchar(255),primary key(id))']cursor = db.cursor()for ddl in sql:        try:                cursor.execute(ddl)        except mysql.Error as err:                if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:                        print "ALREADY EXISTS"                else:                        print err.msg        else:                print 'OK: %s' % ddlinsert_sql = ("insert into t2(id,name) values (%s,%s)")insert_data = [(1,'andy'),(2,'mandy'),(3,'isadba')]for row in insert_data:        try:                cursor.execute(insert_sql,row)        except mysql.Error as err:                print err.msgdb.commit()query_sql = 'select id,name from t2 where id = 1'uid = [1,]cursor.execute(query_sql)for id,name in cursor:        print id,name

http://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html