python操作mysql數(shù)據(jù)庫的相關(guān)操作實例

# -*- coding: utf-8 -*-
#python operate mysql database
import MySQLdb
  
#數(shù)據(jù)庫名稱
DATABASE_NAME = ''
#host = 'localhost' or '172.0.0.1'
HOST = ''
#端口號
PORT = ''
#用戶名稱
USER_NAME = ''
#數(shù)據(jù)庫密碼
PASSWORD = ''
#數(shù)據(jù)庫編碼
CHAR_SET = ''
  
#初始化參數(shù)
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'
      
#獲取數(shù)據(jù)庫連接
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()
  
#關(guān)閉連接
def conn_close(conn):
    if conn != None:
        conn.close()
  
#關(guān)閉cursor
def cursor_close(cursor):
    if cursor != None:
        cursor.close()
  
#關(guān)閉所有
def close(cursor, conn):
    cursor_close(cursor)
    conn_close(conn)
  
#創(chuàng)建表
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:      #循環(huán)每一條數(shù)據(jù)
                #print(r)
        close(cursor, conn)
    else:
        print('table name is empty!')
  
#插入數(shù)據(jù)
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
  
#更新數(shù)據(jù)
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
  
#刪除數(shù)據(jù)
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
  
#數(shù)據(jù)庫連接信息  
def print_info():
    print('數(shù)據(jù)庫連接信息:' + DATABASE_NAME + HOST + PORT + USER_NAME + PASSWORD + CHAR_SET)
  
#打印出數(shù)據(jù)庫中表情況
def show_databases():
    sql = 'show databases'
    conn = get_conn()
    cursor = get_cursor(conn)
    result = cursor.execute(sql)
    for row in cursor.fetchall():
        print(row)
          
#數(shù)據(jù)庫中表情況
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()
    #創(chuàng)建表
    result = create_table()
    print(result)
    #查詢表
    query_table('student')
    #插入數(shù)據(jù)
    print(insert_table())
    print('插入數(shù)據(jù)后....')
    query_table('student')
    #更新數(shù)據(jù)
    print(update_table())
    print('更新數(shù)據(jù)后....')
    query_table('student')
    #刪除數(shù)據(jù)
    delete_data()
    print('刪除數(shù)據(jù)后....')
    query_table('student')
    print_info()
    #數(shù)據(jù)庫中表情況
    show_tables()
      
  
if __name__ == '__main__':
    main()

標簽： [db:TAGG]

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請與原作者聯(lián)系。