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()