python批量操作mysql
2018-08-22 本文已影响395人
katelin
工具
source py3/bin/activate
sudo apt-get install python-mysql.connector
mysql基本操作
select * from right_answer limit 10;
delete from zzxs_ftr where username='interviewer148’;
update user set password='12345' where username='interviewer1';
insert into user (username,password) values ('kate','77776666');
#mysql没有top的用法,查询前N条需要用limit
python批量操作mysql
#连接mysql
import mysql.connector
conn=mysql.connector.connect(user='root',password='111',database='label1')
查询
cursor=conn.cursor()
cursor.execute('select username,password from user;')
values=cursor.fetchall()
fetchone()返回单个元组,也就是一条记录,没有结果返回None,fetchall()返回多个元组,通过value[0],value[1]可以依次访问user,password
Python内置的list,有序集合,可以随时添加和删除其中的元素。另一种有序列表元组tuple,一旦初始化就不能修改。
更新
#更新密码
dict={}
for value in values:
dict[value[0]]=value[1]+'s'
dict
#写入mysql
for interviewer in dict:
sql="update user set password ='%s' where username ='%s';" % (dict[interviewer],interviewer)
try:
cursor.execute(sql)
conn.commit()
except:
conn.rollback()
conn.close()
插入多条数据
#
cursor=conn.cursor(buffered=True)
插入多条记录时需要使用缓冲游标,否则会出现Unread错误
def passwd_generator(size=8, chars=string.digits+string.ascii_letters):
return ''.join(random.choice(chars) for _ in range(size))
python随机生成字符串,python3需要使用string.ascii_letters,应该python2是string.letters
#新建账户
interviewers={}
filepath={}
for i in range(152,201):
username='interviewer'+str(i)
password=passwd_generator(8)
interviewers[username]=password
filepath[username]='path'+str(i)
#写入mysql
for interviewer in interviewers:
sql="insert into user (username,password) values ('%s','%s');" % (interviewer,interviewers[interviewer])
#执行及异常处理同上
conn.close()
insert时需要注意表结构:字段类型,主键,是否自增,是否为空等属性。
desc table_name
删除
delete from user where filepath is Null;