- # -*- 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()
来源: http://www.phpxs.com/code/1005222/