mysql 数据库的相关操作
#coding=gbk #数据库的连接语句 import pymysql try: conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', passwd='root', charset='utf8') conn.select_db('grdb') cur=conn.cursor() cur.execute("") cur.close() conn.commit() conn.close() except pymysql.Error as e: print(e) ''' 执行多行sql cur.execute('drop table if exists user;') cur.execute(""" create table user( id int(11) default null, name varchar(200) default null, password varchar(20) default null, birthday date default null)engine=innodb default charset=utf8;""") #insert into 的格式化方式插入数据 sql="insert into user values(%s,%s,%s,%s)" cur.execute(sql,(6,'张三666','221111','1978-11-15')) #测试时批量插入数据 for i in range(100,201): s=str(1900+i-100)+'-3-15' cur.execute(sql,(i,'张三'+str(i),'22111',s)) #获取查询数据fetchone cur.execute("select * from user") while 1: res=cur.fetchone() if res==None: print(res) break #获取多行数据 cur.fetchmany(2) 获取2条数据 #获取所有数据 cur.fetchall() 获取所有数据 #批量更新 executemany cur.executemany("update user set name=%s where id=%s",[('xiaoli',102),('xiaobai',104)]) 后面可以是列表或者元组 cur.executemany("update user set name=%s where id=%s",(('xiaoli',102),('xiaobai',104))) 如果只有子元素,可以是字典的items() cur.executemany("update user set name=%s where id=%s",{'xiaoli2':102,'xiaohei2':103,'xiaobai2':104}.items()) #delete多行 cur.executemany("delete from uer where id =%s and name=%s",[(101,'heihei'),(104,'xiaoli')]) cur.executemany("delete from uer where id =%s;"[(101,),(104,)]) #事务回滚 conn.rollback() conn.commit()之前 '''