python之mysql数据库操作

来源：互联网发布：初中英语辅导软件编辑：程序博客网时间：2024/05/08 00:31

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

# -*- coding: cp936 -*-# mysql examplesimport mysql.connectorfrom mysql.connector import errorcode# 连接数据库# 1cnx = mysql.connector.connect(user = 'xmltest',                              password = '123456',                              host = '127.0.0.1',                              database = 'txml')# 2try:    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)# 3config = {    'user':'xmltest',    'password':'123456',    'host':'127.0.0.1',    'database':'txml'    }cnx = mysql.connector.connect(**config)

2. 创建数据库:

# -*- coding: cp936 -*-# mysql examplesimport mysql.connectorfrom 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 examplesimport mysql.connectorfrom 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 examplesimport mysql.connectorfrom mysql.connector import errorcodefrom datetime import date, datetime, timedeltaif __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()

0 0