pymysql保存数组_pymysql库的学习

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')