python操作数据库mySQL

mySQL：8.0
python：3.7

mySQL基本操作

1. 连接数据库 mydb mycursor（游标，为用户开设的数据缓冲区，存放SQL语句的执行结果）

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="123456",
    #database="testdb"  # 连接到数据库schema
)
mycursor = mydb.mycursor()

2. 创建、删除数据库

mycursor.execute("CREATE DATABASE testdb")  #创建数据库
mycursor.execute("DROP DATABASE testdb")    #移除数据库

mycursor.execute("SHOW DATABASES")          #显示数据库结构
print(mycursor)
for db in mycursor:
	print(db)

3. 创建表、删除表

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

4. 插入 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()

5. 查找 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)

6. 修改表 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)

7. 删除 DELETE FROM _ WHERE _

# 删数据
sql = "DELETE FROM students WHERE name = 'Bob'"
mycursor.execute(sql)
mydb.commit()

读csv到mySQL

通过executemany批量导入，比mySQL workbench中逐条导入效率高很多

1. 首先连接到数据库schema
2. 读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)

3. 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

1. 连接到数据库schema：mydb mycursor
2. 从数据库读数组

sqlcom = "select time, position, indoor_temp from select_indoor_temp_back14"
df = pd.read_sql(sqlcom, mydb)
df = np.array(df)

存数组到mySQL

1. 连接到数据库schema：mydb mycursor
2. 从数据库读数组
   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()