精华内容
下载资源
问答
  • I'm trying to read a few hundred tables from ascii and then write them to mySQL. It seems easy to do with Pandas but I hit an error that doesn't make sense to me:I have a data frame of 8 columns. He.....

    I'm trying to read a few hundred tables from ascii and then write them to mySQL. It seems easy to do with Pandas but I hit an error that doesn't make sense to me:

    I have a data frame of 8 columns. Here is the column list/index:

    metricDF.columns

    Index([u'FID', u'TYPE', u'CO', u'CITY', u'LINENO', u'SUBLINE', u'VALUE_010', u'VALUE2_015'], dtype=object)

    I then use to_sql to append the data up to mySQL

    metricDF.to_sql(con=con, name=seqFile, if_exists='append', flavor='mysql')

    I get a strange error about a column being "nan":

    OperationalError: (1054, "Unknown column 'nan' in 'field list'")

    As you can see all my columns have names. I realize mysql/sql support for writing appears in development so perhaps that's the reason? If so is there a work around? Any suggestions would be greatly appreciated.

    解决方案

    Update: starting with pandas 0.15, to_sql supports writing NaN values (they will be written as NULL in the database), so the workaround described below should not be needed anymore (see https://github.com/pydata/pandas/pull/8208).

    Pandas 0.15 will be released in coming October, and the feature is merged in the development version.

    This is probably due to NaN values in your table, and this is a known shortcoming at the moment that the pandas sql functions don't handle NaNs well (https://github.com/pydata/pandas/issues/2754, https://github.com/pydata/pandas/issues/4199)

    As a workaround at this moment (for pandas versions 0.14.1 and lower), you can manually convert the nan values to None with:

    df2 = df.astype(object).where(pd.notnull(df), None)

    and then write the dataframe to sql. This however converts all columns to object dtype. Because of this, you have to create the database table based on the original dataframe. Eg if your first row does not contain NaNs:

    df[:1].to_sql('table_name', con)

    df2[1:].to_sql('table_name', con, if_exists='append')

    展开全文
  • 1、使用pandas连接mysql。 2、使用Pandas连接mysql查询读取mysql数据。 3、使用Pandas连接mysql 并插入数据。

    目录

    1、使用pandas连接mysql

    2、使用Pandas连接mysql查询读取mysql数据

    3、使用Pandas连接mysql 并插入数据


    1、使用pandas连接mysql

    需要安装的库:pip install pymysql     pip install sqlalchemy

    代码一(方法1):

    import pandas as pd
    import pymysql
    from sqlalchemy import create_engine
    
    # 初始化数据库连接,使用pymysql模块
    # MySQL的用户:root, 密码:123456, 端口:3306,数据库:lean2
    con = create_engine('mysql+pymysql://root:123456@localhost:3306/learn2')

    代码二(方法2):

    import pandas as pd
    import pymysql
    con=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='131129',db='learn2',use_unicode=True, charset="utf8")
    

    2、使用Pandas连接mysql查询读取mysql数据pd.read_sql_query(sql, con = con)

    import pandas as pd
    import pymysql
    from sqlalchemy import create_engine
    
    # 初始化数据库连接,使用pymysql模块
    # MySQL的用户:root, 密码:123456, 端口:3306,数据库:lean2
    con = create_engine('mysql+pymysql://root:123456@localhost:3306/learn2')
    
    
    # 查询语句,选出emp表中的所有数据
    sql = ''' select * from emp; '''
    # read_sql_query的两个参数: sql语句, 数据库连接
    df = pd.read_sql_query(sql, con = con)
    # 输出employee表的查询结果
    print(df)

    3、使用Pandas连接mysql 并插入数据

    df.to_sql('tablename',con=con,index=ture/false,if_exists='append/fail/replace')

    import pandas as pd
    import pymysql
    from sqlalchemy import create_engine
    
    # 初始化数据库连接,使用pymysql模块
    # MySQL的用户:root, 密码:123456, 端口:3306,数据库:lean2
    con = create_engine('mysql+pymysql://root:123456@localhost:3306/learn2')
    
    
    #直接写入数据-->mydf-->mydf表会自动创建
    # 新建pandas中的DataFrame, 只有id,num两列
    df = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['jalen', 'xr', 'lili', 'liuliu']})
    # 将新建的DataFrame储存为MySQL中的数据表,储存index列
    df.to_sql('mydf', con, index=True)
    print('Read from and write to Mysql table successfully!')
    
    # fail的意思如果表存在,啥也不做
    # replace的意思,如果表存在,删了表,再建立一个新表,把数据插入
    # append的意思,如果表存在,把数据插入,如果表不存在创建一个表!!
    #在存在的表中追加数据-->mydf
    df1 = pd.DataFrame({'id': [5, 6, 7, 8], 'name': ['jalen', 'xr', 'lili', 'liuliu']})
    df1.to_sql('mydf', con, index=True,if_exists='append')
    print('Append data to mysql database successfully!')

     

    展开全文
  • pandas 写入mysql数据库.to_sql方法详解

    千次阅读 2020-11-22 10:29:08
    pandas 写入mysql数据库.to_sql方法详解 DataFrame.to_sql(self,name,con,schema=None,if_exists=’fail’,index=True,index_label=None,chunksize=None,dtype=None,method=None) 再官方文档中已经详细描述to_...

    pandas 写入mysql数据库.to_sql方法详解

    DataFrame.to_sql(selfnameconschema=Noneif_exists=’fail’index=Trueindex_label=Nonechunksize=None,dtype=Nonemethod=None)

     

    再官方文档中已经详细描述to_sql()方法使用方式

    .to_sql()方法的定义:

    def to_sql(self, name, con, schema=None, if_exists='fail', index=True,
               index_label=None, chunksize=None, dtype=None, method=None):
        """
        Write records stored in a DataFrame to a SQL database.
    
        Databases supported by SQLAlchemy [1]_ are supported. Tables can be
        newly created, appended to, or overwritten.
    
        Parameters
        ----------
        name : string
            Name of SQL table.
        con : sqlalchemy.engine.Engine or sqlite3.Connection
            Using SQLAlchemy makes it possible to use any DB supported by that
            library. Legacy support is provided for sqlite3.Connection objects.
    ....

    to_sql()方法参数解析:

    name:指定的是将输入接入数据库当做的哪个表

    con:与数据库链接的方式,推荐使用sqlalchemy的engine类型

    schema: 相应数据库的引擎,不设置则使用数据库的默认引擎,如mysql中的innodb引擎

    if_exists: 当数据库中已经存在数据表时对数据表的操作,有replace替换、append追加,fail则当表存在时提示ValueError

    index:对DataFrame的index索引的处理,为True时索引也将作为数据写入数据表

    index_label:当上一个参数index为True时,设置写入数据表时index的列名称

    chunsize:设置整数,如20000,一次写入数据时的数据行数量,当数据量很大时,需要设置,否则会链接超时写入失败。

    dtype:写入数据表时,可以设置列的名称(The keys should be the column
    names and the values should be the SQLAlchemy types or strings for
    the sqlite3 legacy mode
    ),需要设置时,类型需要和sqlalchemy的类型保持一致.当不设置时,to_sql生成表时会自动兼容最大的类型。

    .to_sql()参数中除 name、con必填外,可选参数index推荐使用False,同时dtype推荐不使用。

    to_sql方法当数据表不存在时创建,存在时根据if_exists参数设置的replace,append,fail时分别对应替换、追加、失败处理。

    数据库中对表的增删改,最好是在数据库层面处理,不应该由to_sql()方法设置,虽然这个方法本身可以在表不存在时增加表,但是不推荐。在数据库层面设计表,需要根据表的数据,不同的字段设计合理的存储类型,可以对表进行合理的设计和优化。to_sql()本身创建的表,浮点类型是double,整型bigint,字符类型默认兼容最大的text,虽然可以使用dtype参数设置类型,但我个人不推荐使用。还是建议在数据库中先创建合理的目标表,在根据to_sql()方法,将数据写入目标表中。

    在Python3中,需要安装sqlalchemy 和 mysql-connector、pymsyql库

    在python3中,to_sql()的con对象,是 sqlalchemy 的 engine 引擎,通过sqlalchemy的create_engine创建:有两种方式,基本格式一致,区别只是在于使用mysqldb,还是使用mysqlconnector,推荐使用mysqlconnector。

    mysqldb是python2的mysql连接库,在python3时,已经废除mysqldb,改为pymysql。在sqlachemy必须使用mysqldb驱动时,需要先导入pymysql,然后pymysql.install_as_MySQLdb()才能使用。

    sqlalchemy.create_engine()的两种方式:

    engine = create_engine('mysql+mysqldb://user:passwd@127.0.0.1/database?charset=utf8') 
    engine = create_engine('mysql+mysqlconnector:// user:passwd@127.0.0.1/database?charset=utf8') 
    user:passwd@127.0.0.1/database  --> 格式为 用户名:密码@服务器地址/数据库名

    mysqlconnector的使用方式

    生成engine对象时,推荐使用mysqlconnector作为驱动,需要使用到sqlalchemy 和 mysql-connector 两个库,使用pip安装

    pip install sqlalchemy

    pin isntall mysql-connector

    固定语法:

    from sqlalchemy import create_engine
    DB_STRING = 'mysql+mysqlconnector://user:passwd@127.0.0.1/database?charset=utf8'
    engine = create_engine(DB_STRING)
    .....
    data.to_sql('表明',con = engine,..)

    这种使用方式在mysql5.7版本以前,是没有问题,但是在mysql8版本以后,mysql8更改了密码加密方式,在使用此种方式时会提示错误。

    在用to_sql写入mysql8以上版本时,需要使用mysqldb作为驱动

    pymysq的使用方式

    pin install pymysql

    在导入pymysq使用时需要pymysql.install_as_MySQLdb()才能使用

    固定语法:

    import pymysql
    pymysql.install_as_MySQLdb()
    DB_STRING = 'mysql+mysqldb://user:passwd@127.0.0.1/db_name?charset=utf8'
    engine = create_engine(DB_STRING) 

    生成引擎之后,可以使用DataFrame.to_sql()方法,将DataFrame数据写入数据库。这种方式本身没有问题,但是在写入数据库时会提示预警信息,不影响正常写入。

    to_sql()时对应的字段类型设置参数dtype使用方法:

    DATE,CHAR,VARCHAR… 可以去 sqlalchemy 的官方文档查看所有的sql数据类型: [‘TypeEngine’, ‘TypeDecorator’, ‘UserDefinedType’, ‘INT’, ‘CHAR’, ‘VARCHAR’, ‘NCHAR’, ‘NVARCHAR’, ‘TEXT’, ‘Text’, ‘FLOAT’, ‘NUMERIC’, ‘REAL’, ‘DECIMAL’, ‘TIMESTAMP’, ‘DATETIME’, ‘CLOB’, ‘BLOB’, ‘BINARY’, ‘VARBINARY’, ‘BOOLEAN’, ‘BIGINT’, ‘SMALLINT’, ‘INTEGER’, ‘DATE’, ‘TIME’, ‘String’, ‘Integer’, ‘SmallInteger’, ‘BigInteger’, ‘Numeric’, ‘Float’, ‘DateTime’, ‘Date’, ‘Time’, ‘LargeBinary’, ‘Binary’, ‘Boolean’, ‘Unicode’, ‘Concatenable’, ‘UnicodeText’, ‘PickleType’, ‘Interval’, ‘Enum’, ‘Indexable’, ‘ARRAY’, ‘JSON’] 可以选择合适的类型与数据库对应

    示例:
    from sqlalchemy.types import DATE,CHAR,VARCHAR 
    DTYPES = {'col_1字段名称' : DATE, 'col_2':CHAR(4),'col_3':VARCHAR(10)}
    df.to_sql(....,dtype = DTYPES)
    将写入数据表的df中,dtype 指定 根据列名对应的数据类型字段即可

    如果使用.to_sql()需要指定dtype类型时,如果数据库中不存在目标表,则相应创建;如果数据库中已经存在目标表,则设置append追加模式写入数据库时,可能会引起字段类型冲突。

    .to_sql()方法参数演示

    data = pd.DataFrame(np.random.rand(4,4),index=list('abcd'),columns=['col_1','col_2','col_3','col_4'])
    print(data)
      col_1     col_2     col_3     col_4
    
     a  0.526716  0.082858  0.458375  0.640027
     b  0.316326  0.122944  0.469743  0.119170
     c  0.911248  0.920943  0.120026  0.165420
     d  0.919385  0.669661  0.083722  0.227291
    将data写入数据库,如果表存在就替换,将data的index也写入数据表,写入字段名称为id_name
    data.to_sql('table_name',con='engine',chunksize=10000,if_exists='replace',index=True,index_label='id_name')
    将data写入数据库,如果表存在就追加
    data.to_sql('table_name',con='engine',chunksize=10000,if_exists='append')
    将data写入数据库,如果表存在就替换,指定col_1的字段类型为char(4)
    data.to_sql('table_name',con='engine',chunksize=10000,if_exists='replace,dtype={'col_1':CHAR(4)})
    
    如果data数据量大,需要设置合理的chunksize值,这和数据库缓存大小有关,
    可以设置在50000-10000,如果提示数据库连接超时错误,就将size值调小。

     

    展开全文
  • 安装相关依赖 pip install pymysql ...读取数据 import pandas as pd import pymysql #连接mysql数据库 db = pymysql.connect(host='127.0.0.1',port=3306,user=username,passwd=passwd,db=database) #设置游...

    安装相关依赖

     pip install pymysql
     pip install sqlalchemy
    

    读取数据

    import pandas as pd
    import pymysql
     
    #连接mysql数据库
    db = pymysql.connect(host='127.0.0.1',port=3306,user=username,passwd=passwd,db=database)
    #设置游标
    cursor = db.cursor()
     
    #编辑相关查询语句,由于python对于sql语句的纠错能力差,建议先在navicat执行一次查询后复制代码到此处
    #长
    sql = ''' select * from test_data'''
     
    #通过游标执行相关sql语句
    cursor1.execute(sql)
    #查询所有的查询结果,此处还可使用.fetchone()查询一个,.fetchmany()查询多个
    data = cursor.fetchall()
     
    #将查询结果转换成dataframe格式,若不需要可以删除
    data = pd.DataFrame(list(data))
     
    #关闭游标和数据库
    cursor.close()
    db.close()
    

    dataframe格式的df写入数据

    import pymysql
    import pandas as pd
    from sqlalchemy import create_engine
    #建立连接,username替换为用户名,passwd替换为密码,test_database替换为数据库名
    conn = create_engine('mysql+pymysql://username:passwd@localhost:3306/test_database',encoding='utf8')  
    #写入数据,table_name为表名,‘replace’表示如果同名表存在就替换掉
    pd.io.sql.to_sql(df, "table_name", conn, if_exists='replace')
    

    to_sql函数
    pandas.DataFrame.to_sql
    DataFrame.to_sql(self, name: str, con, schema=None, if_exists: str = ‘fail’, index: bool = True, index_label=None, chunksize=None, dtype=None, method=None)
    在这里插入图片描述

    展开全文
  • Pandas写入数据MySQL

    千次阅读 2017-01-25 10:51:07
    #-*- coding:utf-8 -*-from sqlalchemy import create_engineclass mysql_engine(): user='******' passwd='******' host='******' port = '******' db_name='******' engine = create_engin
  • 【warning】 pandas 写入MySQL错误

    千次阅读 2018-08-25 23:26:45
    背景:pandas 利用sqlalchemy 存入数据mysql  数据,有中文字符; //////////////////////////////////////////////////////针对linux//////////////////////////////////////////////////// 第一:错误码 ...
  • I'm trying to write a pandas dataframe to MySQL database with following code.import pandas as pdimport numpy as npfrom pandas.io import sqlimport MySQLdbdf = pd.DataFrame([[1.1, 1.1, 1.1, 2.6, 2.5, 3....
  • I'm trying to write a Python Pandas Dataframe to a MySQL database. I realize that it's possible to use sqlalchemy for this, but I'm wondering if there is another way that may be easier, preferably alr...
  • import pandas as pd 导入pandas from config.mysql_conf import mysql_conf 导入数据库的配置文件 from sqlalchemy import create_engine 导入...通过pandas读取Excel文件里的测试数据 data=pd.read_excel(r"C:\Use
  • Pandas读写MySQL数据

    2019-08-23 14:29:34
    利用PandasMySQL数据 # 导入相关库 import pandas as pd from sqlalchemy import create_engine # 初始化数据库连接,使用pymysql模块 # MySQL的用户:root, 密码:123456, 端口:3306,数据库:test engine = ...
  • 最近在编写业务系统时,要增加每种类型上百台设备,在前端web页面进行设备的增加很浪费时间,也不是很现实,只能先将设备信息在EXCEL里编辑好实现批量上传到mysql数据库中;笔者脑海中及时就想到了用pandas里的read_...
  • pandas的DataFrame数据写入MySQL数据库 + sqlalchemy import pandas as pd from sqlalchemy import create_engine ##将数据写入mysql的数据库,但需要先通过sqlalchemy.create_engine建立连接,且字符编码...
  • 我正在尝试使用以下代码将pandas数据写入MySQL数据库.import pandas as pdimport numpy as npfrom pandas.io import sqlimport MySQLdbdf = pd.DataFrame([[1.1, 1.1, 1.1, 2.6, 2.5, 3.4,2.6,2.6,3.4,3.4,2.6,1.1...
  • pandas 清洗 MySQL 数据

    2021-04-14 09:52:19
    import pandas as pd self.conn = pymysql.connect(host=host, user=user, password=pass, db=db, charset='utf8') sql = 'select * from table_name' df = pd.read_sql(sql, con=self.conn) 空值空格处理 处理...
  • 实现的功能:通过pymysql连接MySQL数据库,查询所需数据,然后使用pandas写入到csv文件,并且写入包含数据列名。 import sys import pandas as pd import pymysql import time # 获取当前日期 day=time.strftime("%...
  • 1、Mysql (1)插入数据 因为pymysql不识别除%s之外的其他字符,例如%d,%f,因此,将sql语句的values全部设置为%s def insertdata(data,table_name,connect): c_name = str(data.columns.tolist()).replace(...
  • I have a Pandas DataFrame (called df), which I would like to upload to a MySql database.The dataframe has columns [A, B, C] and the table in the database has columns [ID, A, B, C]. The ID column in th...
  • 先看下需要存入的df数据:安装并导入需要的库:先创建数据库:开始直接一条pandas入库:入库后查看数据:注意:(1)再进行入库的时候,他会根据列自动选择类型,可能为double、float等,后面如果出现类型不匹配,就...
  • Python编程:pandas读写mysql数据

    千次阅读 2019-03-09 11:44:14
    pip install pandas sqlalchemy pymysql 1、建表 # 建表 create table student( id int(11) primary key auto_increment, name varchar(10) default "", age int(11) default 0 ) 如果不创建表...
  • Pandas基础注:以下使用 Jupyter Notebook 演示7 数据库数据内容的读取和写入数据库除了之前介绍的csv、excel类数据外,也是可以利用pandas直接操作的数据来源pandas.read_sql 可以在数据库中执行指定的SQL语句查询...
  • 记一次 pandas 大数据写入mysql内存溢出问题 pandas中dateframe的to_mysql方法可以将df数据存入Mysql,但是,数据量太大时,会报merroy erro 错误,原因就是存入mysql时内存溢出了,这是不要忘记方法中: DataFrame....
  • sqlalchemy链接mysql有多个办法 选择 1 engine = create_engine('mysql+pymysql://用户名:密码@127.0.0.1:端口号3306/数据库名?charset=utf8') 或者选择2  engine = create_engine('mysql+mysqld
  • import pandas as pd import pymysql from sqlalchemy import create_engine yconnect = create_engine('mysql+pymysql://root:root@127.0.0.0:3306/datepi?charset=utf8') qiyetimelongsql='''select did,ma...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,617
精华内容 1,046
关键字:

pandas写入mysql数据

mysql 订阅