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