python操作mysql数据库

python操作mysql数据库的相关操作实例

# -*- 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 row in 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 row in cursor.fetchall():
        print(row)
         
#数据库中表情况
def show_tables():
    sql = "show tables"
    conn = get_conn()
    cursor = get_cursor(conn)
    result = cursor.execute(sql)
    for row in 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()

来源：PY学习网：原文地址：https://www.py.cn/article.html