本文转载
作者:OrangeHolic
链接:http://orangeholic.iteye.com/blog/1714631
在此介绍基本操作,如果有点E文基础,也可看官方文档 http://dev.mysql.com/doc/refman/5.6/en/connector-python.html
1.连接数据库
- import mysql.connector
- config={'host':'127.0.0.1',#默认127.0.0.1
- 'user':'root',
- 'password':'123456',
- 'port':3306 ,#默认即为3306
- 'database':'test',
- 'charset':'utf8'#默认即为utf8
- }
- try:
- cnn=mysql.connector.connect(**config)
- except mysql.connector.Error as e:
- print('connect fails!{}'.format(e))
2.执行建表语句,我们将建下面样式的表
- sql_create_table='CREATE TABLE `student` \
- (`id` int(10) NOT NULL AUTO_INCREMENT,\
- `name` varchar(10) DEFAULT NULL,\
- `age` int(3) DEFAULT NULL,\
- PRIMARY KEY (`id`)) \
- ENGINE=MyISAM DEFAULT CHARSET=utf8'
- cursor=cnn.cursor()
- try:
- cursor.execute(sql_create_table)
- except mysql.connector.Error as e:
- print('create table orange fails!{}'.format(e))
3.插入操作
- cursor=cnn.cursor()
- try:
- '第一种:直接字符串插入方式'
- sql_insert1="insert into student (name, age) values ('orange', 20)"
- cursor.execute(sql_insert1)
- '第二种:元组连接插入方式'
- sql_insert2="insert into student (name, age) values (%s, %s)"
- #此处的%s为占位符,而不是格式化字符串,所以age用%s
- data=('shiki',25)
- cursor.execute(sql_insert2,data)
- '第三种:字典连接插入方式'
- sql_insert3="insert into student (name, age) values (%(name)s, %(age)s)"
- data={'name':'mumu','age':30}
- cursor.execute(sql_insert3,data)
- #如果数据库引擎为Innodb,执行完成后需执行cnn.commit()进行事务提交
- except mysql.connector.Error as e:
- print('insert datas error!{}'.format(e))
- finally:
- cursor.close()
- cnn.close()
还可以多次插入,提高效率
- stmt='insert into student (name, age) values (%s,%s)'
- data=[
- ('Lucy',21),
- ('Tom',22),
- ('Lily',21)]
- cursor.executemany(stmt,data)
4.查询操作
- cursor=cnn.cursor()
- try:
- sql_query='select id,name from student where age > %s'
- cursor.execute(sql_query,(21,))
- for id,name in cursor:
- print ('%s\'s age is older than 25,and her/his id is %d'%(name,id))
- except mysql.connector.Error as e:
- print('query error!{}'.format(e))
- finally:
- cursor.close()
- cnn.close()
5.删除操作
- cursor=cnn.cursor()
- try:
- sql_delete='delete from student where name = %(name)s and age < %(age)s'
- data={'name':'orange','age':24}
- cursor.execute(sql_delete,data)
- except mysql.connector.Error as e:
- print('delete error!{}'.format(e))
- finally:
- cursor.close()
- cnn.close()