python操作mysql資料庫

python操作mysql資料庫的相關操作執行個體

# -*- coding: utf-8 -*-#python operate mysql databaseimport 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)  #擷取cursordef get_cursor(conn):    return conn.cursor()  #關閉串連def conn_close(conn):    if conn != None:        conn.close()  #關閉cursordef 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()

