python链接mysql数据库
python mysql链接库
python的mysql库有mysql官方的版本。
下载地址:http://dev.mysql.com/downloads/connector/python/
文档地址:http://dev.mysql.com/doc/connector-python/
connect方法参数
connect参数说明:http://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html
python链接mysql示例代码
from __future__ import print_function
import mysql.connector
config = {
'host': '127.0.0.1',
'user': 'root',
'password': '123456',
'database': 'py',
'charset': 'utf8',
'autocommit': True,
'raise_on_warnings': True,
}
sql_create_database = """
CREATE DATABASE `py` DEFAULT CHARACTER SET utf8;
"""
sql_create_table = """
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(16) NOT NULL,
`password` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
"""
sql_insert_list = """insert into user(username, password) values(%s, %s);"""
sql_insert_list_params = ('admin14', 'adminpwd')
sql_insert_dict = """insert into user(username, password) values(%(username)s, %(password)s);"""
sql_insert_dict_params = {'username':'admin15', 'password':'adminpwd'}
sql_select = """select * from user where id > %s"""
sql_select_param = (1,)
sql_select_one = """select id, username from user limit 1"""
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
# 创建数据库
cursor.execute(sql_create_database)
# 创建表
cursor.execute(sql_create_table)
# 插入数据 list
cursor.execute(sql_insert_list, sql_insert_list_params)
print(cursor.lastrowid)
#cnx.commit()
# 插入数据 dict
cursor.execute(sql_insert_dict, sql_insert_dict_params)
print(cursor.lastrowid)
#cnx.commit()
# 查询数据 多行
cursor.execute(sql_select, sql_select_param)
#for id, username, password in cursor:
# print(id, username, password)
print(cursor.fetchall())
# 查询数据 单行
result = cursor.execute(sql_select_one)
print(cursor.fetchone())
# 列名
print(cursor.column_names)
# 关闭游标
cursor.close()
# 关闭链接
cnx.close()