pandas数据读写入MySQL数据库 2019/1/29

1.数据类型

sqlalchemypythonmysql描述
Booleanbooltinyint布尔类型，映射到数据库中的是tinyint类型
Integerintint整形，映射到数据库中是int类型
Floatfloatfloat32映射到数据库中是float32类型
Double double64双精度浮点类型
DECIMALdecimal.Decimaldecimal定点型;如用于货币计算
Datedatetime.datedate日期只能存储年月日映射到数据库中是date类型
DateTimedatetime.datetimedatetime日期和时间年月日时分秒毫秒
Timedatetime.timetime时间时分秒
Enumstrenum枚举类型
Stringstrvarchar字符串可变字符类型
Textstrtext文本类型映射到数据库中就是text类型
LongTextstrlongtext长文本类型

实例：  

from sqlalchemy import create_engine, Column, Integer, String, Float, Boolean, 
                                         DECIMAL, Enum, Date, DateTime, Time, Text
from sqlalchemy.dialects.mysql import LONGTEXT

2.create_engine()：  

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user_name:password_name@localhost3306/db_name?charset=utf8")
    
create_engine()
用途：
    创建数据库引擎,解析；建立python与mysql数据库的连接
参数：
    mysql： 指要用的数据库
    pymsql： 指需要用到python接mysql的接口程序
    user_name： 指数据库的账户
    password_name： 指数据库的密码
    localhost： 指本地地址,如果是服务器的数据库需要改为如(127.0.0.1)的类似格式
    3306： 指数据库端口
    db_name： 指数据库的名字
    charset=utf8: 指设置数据库编码方式

3函数pd.read_sql()/.DataFrame.to_sql()：  

pd.read_sql(sql,con,index_col = None,coerce_float = True,params = None,
parse_dates = None,columns = None,chunksize = None)#将SQL查询或数据库表读入df
df.to_sql(name,con,schema = None,if_exists ='fail',index = True,index_label = None,
chunksize = None,dtype = None,method = None ) # 将df中的记录写入SQL数据库

参数：   

读参数：
sql ： str或SQLAlchemy文本对象;查询str或表名
con ： SQLAlchemy可连接(引擎/连接）或数据库字符串URI或DBAPI2连接
index_col =None： str,[str] 设置为索引的列MultiIndex
coerce_float=True ： 尝试将非str,非数字值转换为浮点
params =None： list,tuple或dict传递给执行方法的参数列表。
parse_dates =None： list或dict解析为日期的列名列表。
columns =None： list读取指定列
chunksize =None： int每个块中的行数;如指定返回一个迭代器

写参数：
    name ： str表名
    con ：   sqlalchemy.engine.Engine或sqlite3.Connection
    schema = None：   str为None使用默认架构。
    if_exists = 'fail'：    {'fail', 'replace', 'append'}表存在报错，替代，附加
    index = True：      将df索引写为列。#用index_label作为表中的列名。
    index_label = None ：索引列的列标签。#index_label = None, index = True用索引名称。
                       # 如果DataFrame使用MultiIndex,则应该给出一个序列。
    chunksize = None： int, 行;#一次批量写入大小。默认所有行立即写入
    dtype ：          dict列数据类型。#键是列名, 值是SQLAlchemy类型或sqlite3的字符串

    method = None ： {None, ‘multi’, callable}控制使用的SQL插入子句：
                    # None：INSERT子句每行一个
                    # 'multi'：在单个INSERT子句中传递多个值。
                    # callable with signature (pd    _table, conn, keys, data_iter)

===============================================================================================

数据库查询方法 2019/1/29

实例1：pymysql读数据

import pandas as pd
import pymysql

con = pymysql.connect(host='127.0.0.1', user='root', password='root', db='new_futures',
charset='utf8',use_unicode=True)
sql = 'SELECT * FROM new_futures.m1809'
df = pd.read_sql(sql, con=con)
print(df.head())
print(df.dtypes)
con.close()

'''
No name code datetime ... high low close vol
0 0 豆粕 m1809 2019-01-27 14:08:05 ... 3150.0 3000.0 3110.0 2542
1 1 豆粕 m1809 2019-01-27 14:08:05 ... 3000.0 2900.0 2950.0 1542
2 2 豆粕 m1809 2019-01-27 14:08:05 ... 3050.0 2920.0 2950.0 2470
3 3 豆粕 m1809 2019-01-27 14:08:05 ... 3150.0 3000.0 3192.0 3677

[5 rows x 11 columns]
No int64
name object
datetime datetime64[ns]
date object
time timedelta64[ns]
open float64
vol int64
......
dtype: object
'''

实例2：

import pandas as pd
import numpy as np

from sqlalchemy import create_engine
from sqlalchemy.types import Integer,CHAR
import mysql.connector
import pymysql

pandas查询方法汇总：

pd.read_sql_table() #读表
pd.read_sql_query() #使用原始SQL进行查询
pd.has_table('table_name')#检查表是否存在
pd.io.sql.execute() #普通查询

引擎连接：

from sqlalchemy import create_engine

engine = create_engine('postgresql://root:password@localhost:5432/db_name')
engine = create_engine('mysql+mysqldb://root:password@localhost/db_name')
engine = create_engine('oracle://root:password@127.0.0.1:1521/db_name')
engine = create_engine('mssql+pyodbc://db_name')
engine = create_engine("mysql+mysqlconnector://root:root@"
"localhost:3306/new_futures?charset=utf8")

engine = create_engine('sqlite:///db_name.db')#sqlite无主机名 相对路径
engine = create_engine('sqlite:absolute/path/to/db_name.db')

实例2.1：架构支持

df.to_sql('table', engine, schema='other_schema') #指定其它数据库
pd.read_sql_table('table', engine, schema='other_schema')#指定其它数据库

实例2.2：pd.read_sql_table读表

pd.read_sql_table('table_name', engine)
pd.read_sql_table('table_name', engine, index_col='id') #将id指定为索引
pd.read_sql_table('table_name', engine, columns=['No', 'name'])#读2列数据
pd.read_sql_table('table_name', engine, parse_dates=['Date']) #强制将列解析为日期
pd.read_sql_table('table_name', engine,parse_dates={'Date':
{'format': '%Y-%m-%d %H:%M:%S'}})#指定日期格式字符串

实例2.3：pd.read_sql_query查询

# 可用数据库原生语句或SQLAlchemy表达式语言构造(与数据库无关)

pd.read_sql_query('SELECT * FROM data', engine)
pd.read_sql_query("SELECT id, Col_1, Col_2 FROM data WHERE id = 42;", engine)

df = pd.DataFrame(np.random.randn(20, 3), columns=list('abc'))
df.to_sql('data_chunks', engine, index=False)
for chunk in pd.read_sql_query("SELECT * FROM data_chunks",
engine, chunksize=5):#返回迭代器
print(chunk)

实例2.4：sql.execute普通查询

from pandas.io import sql
sql.execute('SELECT * FROM table_name', engine)
sql.execute('INSERT INTO table_name VALUES(?, ?, ?)', engine, params=[('id', 1, 1.2, True)])

实例2.5：SQLAlchemy.text()构造查询：查询中传入变量

import sqlalchemy as sa

pd.read_sql(sa.text('SELECT * FROM tb_name where name=:col_name'),
engine, params={'col_name': 'Tom'})#指定变量查询

===============================================================================================

pandas数据写入mysql数据库 2019/1/29

  

from sqlalchemy import create_engine
from sqlalchemy.types import Integer,CHAR

import pandas as pd
import mysql.connector

# engine0 = create_engine("mysql+pymysql://root:root@localhost:3306/new_futures?
                          charset=utf8")#127.0.0.1
engine = create_engine("mysql+mysqlconnector://root:root@localhost:3306/new_futures?
                       charset=utf8")#127.0.0.1
df=pd.DataFrame({'No':[10,11,12],'name':['Tom','Smith','Bob'],'vol'[2100,None,2300]})
print(df)

'''''''''
   No   name     vol
0  10    Tom  2100.0
1  11  Smith     NaN
2  12    Bob  2300.0
'''

实例：写入数据到数据库

# 写入数据：数据库支持可空整数

df.to_sql('m1809', con=engine,if_exists = 'append', index=False,dtype={"vol": Integer()})       
#指定数据类型,也可不指定
result=engine.execute("SELECT * FROM m1809").fetchall()#查看数据表返回元祖 用engine0会警告
pd.DataFrame(list(result))
    
pd.read_sql('m1809', con=engine)
Logo
CSDN学习社区

CSDN联合极客时间，共同打造面向开发者的精品内容学习社区，助力成长！

更多推荐

嵌入式作业（七）：基于Ardunio的STM32串口通信

嵌入式作业（七）0作业要求1Ardunio 完成STM32的串口通信（1）安装Ardunio IDE（2）stm32串口通信2关于 stduino IDE0作业要求安装 Ardunio IDE 和相关软件支持库，在Ardunio 完成STM32板子的串口通信程序：（1）持续向串口输出“Hello world！”；（2）当接收到“stop!”时，停止输出。网上有一个国人版的MCU集成开发平台， st

avatar CSDN学习社区

JDBC详解

JDBC文章目录JDBC什么是JDBC?JDBC驱动程序:Java使用JDBC访问数据库的步骤:设置classpath:Oracle连接字符串的书写格式:简单的例子:常用数据库的驱动程序及JDBC URL:Oracle数据库:SQL Server数据库MySQL数据库Access数据库PreparedStatement接口:JNDI-数据源（Data Source）与连接池（Connection

avatar CSDN学习社区

“模式识别与机器学习”学习笔记no2.再谈感知机

接**上篇：上篇主要进行了PLA，Pocket算法的理论过程分析和在给定数据集上利用pocket算法对数据集进行分类学习，得到错分数量最少的分类面。上篇中pocket算法的过程已经进行了编程和测试，框架已经建立了起来，这一篇主要上篇中没有提到或涉及不深的几个问题。1.数据集的构造。上篇是直接使用了题目给的向量，这次来根据正态分布来产生数据集。np.random.normal函数可以根据均值和方差生

avatar CSDN学习社区