Python和Mysql交互

1、创建连接

import pymysql

db=pymysql.connect( host='localhost', user='root',password='1234567',db='mysql',charset='utf8')
cursor=db.cursor()

2、创建表

create_table = 'create table if not exists data "\
               "(InvoiceNo varchar(20),"\
               "StockCode varchar(20),"\
               "Description varchar(50),"\
               "Quantity int(20),"\
               "InvoiceDate varchar(50),"\
               "UnitPrice float(20),"\
               "CustomerID int(20),"\
               "Country varchar(20)) charset=utf8 '

3、插入数值

insert_values = 'insert into data' \
                '(InvoiceNo,StockCode,Description,Quantity,InvoiceDate,UnitPrice,CustomerID,Country) ' \
                'VALUES ' \
                '("536365","85123A","WHITE HANGING HEART T-LIGHT HOLDER","6","12/1/2010 8:26","2.55","17850","United Kingdom")'

4、删除表里的数值

delete_values = 'delete from data'

5、删除表

drop_table ='drop table data'

6、执行增删改查语句

try:
	cursor.execute(create_table)
	#cursor.execute(insert_values)
	#cursor.execute(delete_values)
	#cursor.execute(drop_table)
	db.commit()
except:
   # 发生错误时回滚
   db.rollback()

7、关闭游标和连接

cursor.close() 
db.close()