python操作mysql数据库代码，pythonmysql数据库,python操作mysq

python操作mysql数据库的相关操作实例```python

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