Python将本地数据迁移到mysql中

加载包

##安装包：pip install pymysql -i http://mirrors.aliyun.com/pypi/simple/

import numpy as np
from pandasql import sqldf
from pandas import DataFrame
import pymysql
import pandas as pd
from sqlalchemy import create_engine

读取本地数据

data = pd.read_csv("C:\\Users\\Administrator\\Desktop\\V1_data.csv", encoding="gbk")

转换为数据框

如果不是dataframe，需要转换为dataframe
data1 = DataFrame(dict_data,columns=columnsname)

数据框写入MySQL

conn = create_engine('mysql+pymysql://root:123456@192.168.1.666:3306/knowledge_fire',encoding='utf8') ##knowledge_fire是数据库名称
pd.io.sql.to_sql(data1, "table_name", conn, if_exists='replace')    ## ‘replace’表示如果同名表存在就替换掉

Python中查询SQL语句

该操作直接在数据库中进行增改查删，把python的数据集当做数据表，不需要数据库存储，直接使用SQL语句查询，

con = pymysql.connect(host='192.168.1.666', port=3306, user='root', passwd='123456', db='knowledge_fire')
cursor = con.cursor()
sql = "select * from xioahuosuantable"
data=cursor.execute(sql)
print(data)