mySQL:8.0
python:3.7
mySQL基本操作
- 连接数据库 mydb mycursor(游标,为用户开设的数据缓冲区,存放SQL语句的执行结果)
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="123456",
#database="testdb" # 连接到数据库schema
)
mycursor = mydb.mycursor()
- 创建、删除数据库
mycursor.execute("CREATE DATABASE testdb") #创建数据库
mycursor.execute("DROP DATABASE testdb") #移除数据库
mycursor.execute("SHOW DATABASES") #显示数据库结构
print(mycursor)
for db in mycursor:
print(db)
- 创建表、删除表
mycursor.execute("CREATE TABLE students (name VARCHAR(255), age INTEGER(10))") #创建table
mycursor.execute("SHOW TABLES") #显示table
for tb in mycursor:
print(tb)
#删table : DROP TABLE
# sql = "DROP TABLE IF EXISTS students"
# mycursor.execute(sql)
# mydb.commit()
- 插入 INSERT INTO
sqlFormula = "INSERT INTO students (name, age) VALUES (%s, %s)"
# 单个插入
student1 = ("Rachel", 22)
mycursor.execute(sqlFormula, student1)
mydb.commit() #commit()提交修改,需要跟在增insert删delete改updata的execute后面
# 多个插入(效率高)
students = [("Bob", 12),
("Amanda", 32),
("Jacob", 21),
("Avi", 28),
("Michelle", 17)]
mycursor.executemany(sqlFormula, students)
mydb.commit()
- 查找 SELECT * FROM _ WHERE _
# 选择和获取
# 获取全部 按行打印
mycursor.execute("SELECT * FROM students")
myresult = mycursor.fetchall() #fetchall获取数据库记录所有列表 fetchone
for row in myresult:
print(row)
# 按条件 : SELECT FORM WHERE
sql = "SELECT * FROM students WHERE age=17" #WHERE name LIKE 'Mi%' WHERE name LIKE '%ac%'
mycursor.execute(sql)
# sql = "SELECT * FROM students WHERE name = %s"
# mycursor.execute(sql, ("Mike", ))
myresult = mycursor.fetchall()
for result in myresult:
print(result)
- 修改表 UPDATE _ SET _
sql = "UPDATE students SET age = 13 WHERE name = 'Bob'"
mycursor.execute(sql)
mydb.commit()
#限制显示5个 : LIMIT OFFSET
# mycursor.execute("SELECT * FROM students LIMIT 5 OFFSET 2") #不算前2个,限制取5个
myresult = mycursor.fetchall()
for result in myresult:
print(result)
# 排序
sql = "SELECT * FROM students ORDER BY age" #DESC ASC
mycursor.execute(sql)
myresult = mycursor.fetchall()
for r in myresult:
print(r)
- 删除 DELETE FROM _ WHERE _
# 删数据
sql = "DELETE FROM students WHERE name = 'Bob'"
mycursor.execute(sql)
mydb.commit()
读csv到mySQL
通过executemany批量导入,比mySQL workbench中逐条导入效率高很多
- 首先连接到数据库schema
- 读csv文件路径
一个csv文件直接到文件名:
read_csv(r'C:\Users\Administrator\Desktop\qingdao_hot\select_indoor_temp_back14.csv')
读一个文件夹下多个csv文件:
file_list = os.listdir(dir)
for i in range(len(file_list)):
file_path = os.path.join(dir, file_list[i])
if os.path.isfile(file_path):
read_csv(file_path)
- read_csv()函数完成从csv到数据库的读取
import os
import pandas as pd
from pymysql import connect
def read_csv(self, filename):
df = pd.read_csv(filename, keep_default_na=False, encoding='utf-8')
table_name = os.path.split(filename)[-1].split('.')[0].replace(' ', '_')
# table_name = '`' + os.path.split(filename)[-1].split('.')[0].replace(' ', '_') + '`'
field1 = "time DATETIME, position DOUBLE, indoor_temp DOUBLE"
field2 = "time, position, indoor_temp"
mycursor.execute("drop table if exists {0}".format(table_name))
mycursor.execute("CREATE TABLE {} ({})".format(table_name, field1))
values = df.values.tolist()
s = ','.join(['%s' for _ in range(len(df.columns))])
try:
mycursor.executemany('insert into {}({}) values ({})'.format(table_name, field2, s), values)
except Exception as e:
print
mydb.rollback()
finally:
mydb.commit()
mydb.close()
读 / 存数组到mySQL
读数组到mySQL
- 连接到数据库schema:mydb mycursor
- 从数据库读数组
sqlcom = "select time, position, indoor_temp from select_indoor_temp_back14"
df = pd.read_sql(sqlcom, mydb)
df = np.array(df)
存数组到mySQL
- 连接到数据库schema:mydb mycursor
- 从数据库读数组
mySQL中设定的时间类型为DATETIME,而数组中时间的类型为Timestamp时间戳,需要先将日期类型转化Timestamp->DATETIME
import pymysql
import pandas as pd
import numpy as np
import time
def MakeTableSQL(array):
# 对时间的处理:Timestamp->DATETIME
datetime1 = []
for t in array[:, 0]:
t = pd.Timestamp(t)
t = t.timestamp()
a = datetime.datetime.fromtimestamp(t)
b = a.strftime("%Y--%m--%d %H:%M:%S")
datetime1.append(a)
datetime1 = np.array(datetime1)[:, np.newaxis]
values = np.concatenate((datetime1,array[:,1:9]),axis=1)
field1 = "time DATETIME, indoor_temp DOUBLE, water_supply_temp DOUBLE, outdoor_temp DOUBLE, wind_pow DOUBLE, wind_dir int(11), light_time DOUBLE, humidity DOUBLE, air_pre DOUBLE"
field2 = "time, indoor_temp, water_supply_temp, outdoor_temp, wind_pow, wind_dir, light_time, humidity, air_pre"
table_name = "data_all_pre"
cursor.execute("drop table if exists {0}".format(table_name))
cursor.execute("CREATE TABLE {} ({})".format(table_name, field1))
values = values.tolist()
s = ','.join(['%s' for _ in range(len(values[0]))])
try:
mycursor.executemany('insert into {}({}) values ({})'.format(table_name, field2, s), values)
except Exception as e:
print
mydb.rollback()
finally:
mydb.commit()
mydb.close()