大家好，对python操作mysql数据库的相关实例感兴趣的小伙伴，下面一起跟随三零脚本的小编来看看python操作mysql数据库的相关实例的例子吧。

#三零脚本 www.q3060.com
# -*- coding: utf-8 -*-
#python operate mysql database
import MySQLdb
  
#数据库名称
DATABASE_NAME= ''
#host = 'localhost' or '172.0.0.1'
HOST= ''
#端口号
PORT= ''
#用户名称
USER_NAME= ''
#数据库密码
PASSWORD= ''
#数据库编码
CHAR_SET= ''
  
#初始化参数
def init():
    global DATABASE_NAME
    DATABASE_NAME= 'test'
    global HOST
    HOST= 'localhost'
    global PORT
    PORT= '3306'
    global USER_NAME
    USER_NAME= 'root'
    global PASSWORD
    PASSWORD= 'root'
    global CHAR_SET
    CHAR_SET= 'utf8'
      
#获取数据库连接
def get_conn():
    init()
    return MySQLdb.connect(host= HOST, user= USER_NAME, passwd= PASSWORD, db= DATABASE_NAME, charset= CHAR_SET)
  
#获取cursor
def get_cursor(conn):
    return conn.cursor()
  
#关闭连接
def conn_close(conn):
    if conn != None:
        conn.close()
  
#关闭cursor
def cursor_close(cursor):
    if cursor != None:
        cursor.close()
  
#关闭所有
def close(cursor, conn):
    cursor_close(cursor)
    conn_close(conn)
  
#创建表
def create_table():
    sql= '''
    CREATE TABLE `student` (
    `id` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `age` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    '''
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql)
    conn.commit()
    close(cursor, conn)
    return result
  
#查询表信息
def query_table(table_name):
    if table_name != '':
        sql= 'select * from ' + table_name
        conn= get_conn()
        cursor= get_cursor(conn)
        result= cursor.execute(sql)
        for rowin cursor.fetchall():
            print(row)
            #for r in row:      #循环每一条数据
                #print(r)
        close(cursor, conn)
    else:
        print('table name is empty!')
  
#插入数据
def insert_table():
    sql= 'insert into student(id, name, age) values(%s, %s, %s)'
    params= ('1','Hongten_a','21')
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql, params)
    conn.commit()
    close(cursor, conn)
    return result
  
#更新数据
def update_table():
    sql= 'update student set name = %s where id = 1'
    params= ('HONGTEN')
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql, params)
    conn.commit()
    close(cursor, conn)
    return result
  
#删除数据
def delete_data():
    sql= 'delete from student where id = %s'
    params= ('1')
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql, params)
    conn.commit()
    close(cursor, conn)
    return result
  
#数据库连接信息  
def print_info():
    print('数据库连接信息:' + DATABASE_NAME+ HOST+ PORT+ USER_NAME+ PASSWORD+ CHAR_SET)
  
#打印出数据库中表情况
def show_databases():
    sql= 'show databases'
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql)
    for rowin cursor.fetchall():
        print(row)
          
#数据库中表情况
def show_tables():
    sql= 'show tables'
    conn= get_conn()
    cursor= get_cursor(conn)
    result= cursor.execute(sql)
    for rowin cursor.fetchall():
        print(row)
  
     
def main():
    show_tables()
    #创建表
    result= create_table()
    print(result)
    #查询表
    query_table('student')
    #插入数据
    print(insert_table())
    print('插入数据后....')
    query_table('student')
    #更新数据
    print(update_table())
    print('更新数据后....')
    query_table('student')
    #删除数据
    delete_data()
    print('删除数据后....')
    query_table('student')
    print_info()
    #数据库中表情况
    show_tables()
      
  
if __name__== '__main__':
    main()