python链接mysql数据库

2013-10-25 15:04  879人阅读  评论 (0)

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