python之mysql数据库操作

1. mysql数据库服务器连接:

# -*- coding: cp936 -*-
# mysql examples
import mysql.connector
from mysql.connector import errorcode

# 连接数据库
# 1
cnx = mysql.connector.connect(user = 'xmltest',
                              password = '123456',
                              host = '127.0.0.1',
                              database = 'txml')

# 2
try:
    cnx = mysql.connector.connect(user = 'xmltest',
                                  password = '123456',
                                  host = '127.0.0.1',
                                  database = 'txml')
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print ("Something is wrong your username or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print ("Database does not exists")
    else:
        print (err)

# 3
config = {
    'user':'xmltest',
    'password':'123456',
    'host':'127.0.0.1',
    'database':'txml'
    }
cnx = mysql.connector.connect(**config)

 

2. 创建数据库:

# -*- coding: cp936 -*-
# mysql examples
import mysql.connector
from mysql.connector import errorcode

# 创建数据库
def create_database(cursor, DB_NAME):
    try:
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
    except mysql.connector.Error as err:
        print ("Failed creating database: {}".format(err))
        exit(1)
    print 'create database success.'

# 选择数据库:若不存在,则创建
def select_database(cursor, DB_NAME):
    try:
        cnx.database = DB_NAME
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            print 'database is not exist, create it now...'
            create_database(cursor,DB_NAME)
            cnx.database = DB_NAME
        else:
            print err
    print 'select database success.'

3.  创建表

# -*- coding: cp936 -*-
# mysql examples
import mysql.connector
from mysql.connector import errorcode

# 创建表
TABLES = {}
TABLES['employees'] = (
    "CREATE TABLE `employees` ("
    " `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
    " `birth_date` date NOT NULL,"
    " `first_name` varchar(14) NOT NULL,"
    " `last_name` varchar(16) NOT NULL,"
    " `gender` enum('M','F') NOT NULL,"
    " `hire_date` date NOT NULL,"
    " PRIMARY KEY (`emp_no`)"
    ")"
    )

TABLES['departments'] = (
    "CREATE TABLE `departments` ("
    " `dept_no` char(4) NOT NULL,"
    " `dept_name` varchar(40) NOT NULL,"
    " PRIMARY KEY(`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
    ")"
    )
TABLES['salaries'] = (
    "CREATE TABLE `salaries` ("
    " `emp_no` int(11) NOT NULL,"
    " `salary` int(11) NOT NULL,"
    " `from_date` date NOT NULL,"
    " PRIMARY KEY(`emp_no`, `from_date`), KEY `emp_no` (`emp_no`),"
    " CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
    "   REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
    ")"
    )
TABLES['dept_emp'] = (
    "CREATE TABLE `dept_emp` ("
    " `emp_no` int(11) NOT NULL,"
    " `dept_no` char(4) NOT NULL,"
    " `from_date` date NOT NULL,"
    " `to_date` date NOT NULL,"
    " PRIMARY KEY(`emp_no`, `dept_no`), KEY `emp_no` (`emp_no`),"
    " KEY `dept_no` (`dept_no`),"
    " CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
    "   REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
    " CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
    "   REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
    ")"
    )

def create_table(table):
    global cursor

    for key in table:
        try:
            print ("Creating table {}:".format(key))
            cursor.execute(table[key])
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print 'already exist.'
            else:
                print err.errmsg
        else:
            print 'OK'

if __name__ == '__main__':
    config = {
        'user':'xmltest',
        'password':'123456',
        'host':'127.0.0.1',
        'database': 'mytest'
        }
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    create_table(TABLES)
    #cnx.commit()
    cnx.close()

4. 插入数据

# -*- coding: cp936 -*-
# mysql examples
import mysql.connector
from mysql.connector import errorcode
from datetime import date, datetime, timedelta

if __name__ == '__main__':
    config = {
        'user':'xmltest',
        'password':'123456',
        'host':'127.0.0.1',
        'database': 'mytest'
        }
    cnx = mysql.connector.connect(**config)
    cursor = cnx.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 employee
    cursor.execute(add_employee, data_employee)
    cnx.commit()
    emp_no = cursor.lastrowid
    #insert salary information
    data_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 database
    cnx.commit()
    cnx.close()