pymysql基础学习
mysql返回对象和python对象的转换
元组转换为列表
datetime.date对象和字符串的转换
游标和事务管理
游标协调mysql的集合处理方式和python的一行一行处理
事务管理还要懂得sql呀——
pymysql只是一个对接mysql的库,sql语句才是精髓,在使用pymysql的库中,对execute(sql,arg)中的sql语句尽量不包括实际值,用arg替代
库中Date类的时间函数
ctime
day
fromisocalendar
fromisoformat
fromordinal
fromtimestamp
isocalendar
isoformat
isoweekday
max
min
month
replace
resolution
strftime
timetuple
today
toordinal
weekday
year
字符串和datetime.date对象的转换
isoformat() 2000-07-03
isocalendar() (2000, 27, 1) tuple对象
isoweekday() 1-7 int 周一到周日
weekday() 0-6
详细讲解https://www.runoob.com/python/att-time-strftime.html
for date in result:
str=date.strftime('%Y-%m-%d')#位置可以自由组合,个数也可以自由组合
#只可以转换标准的YY-MM-DD
date=Date.fromisoformat(date) #class method
#自由组合例子
# date=date.strftime('%b %d %Y')
import pymysql
#连接数据库
db = pymysql.connect(host='localhost',user='root', password='11111111', port=3306)
cursor = db.cursor()
#创建表,只需执行一次
cursor.execute('use spiders')
sql = 'create table if not exists students (id varchar(255) not null, name varchar(255) not null, age int not null, primary key (id))'
cursor.execute(sql)
#插入
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
if cursor.execute(sql, tuple(data.values())):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()#事务回滚
#更新数据
sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
#如果插入值重复就不插入
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql, tuple(data.values())*2):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
#删除操作
table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
#查询数据要注意,数据量过大要迭代返回
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
row = cursor.fetchone() #fetchmany(size)
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')