import pymysql

database = pymysql.connect("127.0.0.1", "test", "test", "db", charset='utf8')
# 格式：pymysql.connect("MySQL服务器地址", "用户名", "密码", "数据库名", charset='utf8')
cursor = database.cursor()
# 初始化指针


# 增

# 格式："INSERT INTO 表名 (字段1,字段2,字段3) VALUES (内容1,内容2,内容3);"
sql = "INSERT INTO data (date,company,province,price,weight) VALUES ('2019-9-20','河北粮食','河北','2200','45.1');"
cursor.execute(sql)
database.commit()  # 对存储的数据修改后，需要commit
database.close()

# 改
# 格式："UPDATE 表名 SET 字段1=内容1,字段2=内容2  WHERE 条件;"

sql = "UPDATE data SET date='2018-09-21' WHERE DATE='2019-09-20';"
cursor.execute(sql)
database.commit()  # 对存储的数据修改后，需要commit
database.close()

# 查
# 基础语法："SELECT 字段 FROM 表名 WHERE 条件"

sql = "SELECT company FROM data WHERE date='2018-07-21';"
cursor.execute(sql)
result = cursor.fetchall()
print(result)
database.close()

# 删
# 格式："DELETE FROM 表名 WHERE 条件;" 条件的写法 ：字段=内容

sql = "DELETE FROM data WHERE date='2018-09-21';"
cursor.execute(sql)
database.commit()  # 对存储的数据修改后，需要commit
database.close()

import pymysql
import xlrd
import datetime

database = pymysql.connect("127.0.0.1", "test", "test", "db", charset='utf8')
cursor = database.cursor()

sql1 = "DELETE FROM data ;"
cursor.execute(sql1)
database.commit()


def change_date(date_excel):
    first_date = datetime.date(1899, 12, 31).toordinal() - 1
    if isinstance(date_excel, float):
        date_excel = int(date_excel)
    date_mysql = datetime.date.fromordinal(first_date + date_excel)
    return date_mysql.strftime("%Y-%m-%d")


xlsx = xlrd.open_workbook('d:/7月下旬入库表.xlsx')
table = xlsx.sheet_by_index(0)
for i in range(1, table.nrows):
    sql2 = "INSERT INTO data (date,company,province,price,weight) VALUES ('%s','%s','%s','%s','%s');" % (
    change_date(table.cell_value(i, 0)), table.cell_value(i, 1), table.cell_value(i, 2), table.cell_value(i, 3),table.cell_value(i, 4))
    cursor.execute(sql2)
    database.commit()


sql3 ="SELECT SUM(weight) FROM data WHERE date>'2018-07-21' AND date<'2018-07-25' AND company='王五小麦' AND province='河北' "
cursor.execute(sql3)
print(cursor.fetchall()[0][0])

database.close()