Python之MySQLdb
MySQLdb是用于Python链接Mysql数据库的接口,它实现了Python数据库API规范V2.0,基于MySql C API上建立的。
1. MySQLdb安装
(1)安装Mysql,参考上篇博客数据库之MySql。
(2)使用pip安装MySQLdb:pip install MySQL-python
但是安装的时候会报错:error: command 'C:\\Program Files\\Microsoft Visual Studio 14.0\\VC\\BIN\\cl.exe' failed with exit status 2
下面推荐两种方法进行解决:
a. 下载Python-3.5及上版本扩展的mysql驱动:https://pypi.python.org/pypi/mysqlclient/1.3.10
之后将下载后的*.whl文件跟pip.exe放在同个目录(一般是在 ..\Python36\Scripts\ 里)
然后用cmd命令进入到这个目录执行PIP命令安装:pip install mysqlclient-1.3.10-cp36-cp36m-win32.whl
b. 安装pymysql代替:pip install pymysql
注:以上安装方法内容来自原文https://www.cnblogs.com/bu1tcat/p/8283742.html
2. MySQLdb实例
#coding=utf-8 import sys import MySQLdb as db import csv def parse_csv(csvfile): with open(csvfile, 'r') as pf: reader = csv.reader(pf, delimiter=',') header = next(reader) csvdata = [] for row in reader: csvdata.append(row) return header, csvdata #链接数据库 def mysqldb_operator(data): conn = db.connect(host="localhost", user="root", passwd="", db="xbqr", charset="utf8") print (conn) print ("datebase connect success!") curs = conn.cursor() #创建表之前先删除表 curs.execute("drop table IF EXISTS table_xbqr") conn.commit() #创建表 query = "create table table_xbqr(\ SupplierName VARCHAR(32),\ InvoiceNumber VARCHAR(32),\ PartNumber VARCHAR(32),\ Cost VARCHAR(32),\ PurchaseDate DATE)" curs.execute(query) conn.commit() for row in data: curs.execute("INSERT INTO table_xbqr VALUES(%s,%s,%s,%s,%s);", row) conn.commit() curs.execute("select * from table_xbqr") conn.commit() selectdata = curs.fetchall() print (selectdata) if __name__ == "__main__": csvfile = sys.argv[1] print ("csv file name:", csvfile) header, data = parse_csv(csvfile) print ("header:") print (header) print ("csvdata:") print (data) mysqldb_operator(data)