加载包
##安装包: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)