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 c<b style="color:transparent">本文来源gao@!dai!ma.com搞$$代^@码!网!</b>ursor.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()

喜欢 (0)赏

[搞代码]

分享 (0)