python – 使用sqlalchemy将CSV导入数据库

概述我使用 this示例将csv文件上传到sqlite数据库： 这是我的代码： from numpy import genfromtxtfrom time import timefrom datetime import datetimefrom sqlalchemy import Column, Integer, Float, Date, String, VARCHARfrom sqlalch 我使用 this示例将csv文件上传到sqlite数据库：

这是我的代码：

from numpy import genfromtxtfrom time import timefrom datetime import datetimefrom sqlalchemy import Column,Integer,float,Date,String,VARCHARfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerdef Load_Data(file_name):    data = genfromtxt(file_name,delimiter=',')# skiprows=1,converters={0: lambda s: str(s)})    return data.toList()Base = declarative_base()class cdb1(Base):    #Tell sqlAlchemy what the table name is and if there's any table-specific arguments it should kNow about    __tablename__ = 'cdb1'    __table_args__ = {'sqlite_autoincrement': True}    #tell sqlAlchemy the name of column and its attributes:    ID = Column(Integer,primary_key=True,nullable=False)     name = Column(VARCHAR(40))    sHack = Column(VARCHAR)    db = Column(Integer)    payments = Column(Integer)    status = Column(VARCHAR)if __name__ == "__main__":    t = time()    print 'creating database'    #Create the database    engine = create_engine('sqlite:///cdb.db')    Base.Metadata.create_all(engine)    #Create the session    session = sessionmaker()    session.configure(bind=engine)    s = session()    try:        file_name = 'clIEnt_db.csv'        data = Load_Data(file_name)        for i in data:            record = cdb1(**{                'name' : i[0],'sHack' : i[1],'db' : i[2],'payments' : i[3],'status' : i[4]            })            s.add(record) #Add all the records        s.commit() #Attempt to commit all the records    except:        s.rollback() #Rollback the changes on error        print 'error in reading'    finally:        s.close() #Close the connection    print "Time elapsed: " + str(time() - t) + " s." #0.091s

这是csv文件的前几行：

name,SHack,DB,Payments,StatusLoyiso Dwala,I156,13542,37,lightsOnly ONAttwell Fayo,I157,13077,32,lightsONDavID Mbhele,G25,13155,33,lightsON

数据库创建正常,但只有部分数据被捕获到属性中：’payments’和’db’列被正确填充,但其他所有数据都是NulL.

更新的正确代码(使用pandas数据帧)：

from numpy import genfromtxtfrom time import timefrom datetime import datetimefrom sqlalchemy import Column,VARCHARfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerimport csvimport pandas as pd#def Load_Data(file_name):    #data = csv.reader(file_name,converters={0: lambda s: str(s)})    #return data.toList()Base = declarative_base()class cdb1(Base):    #Tell sqlAlchemy what the table name is and if there's any table-specific arguments it should kNow about    __tablename__ = 'cdb1'    __table_args__ = {'sqlite_autoincrement': True}    #tell sqlAlchemy the name of column and its attributes:    ID = Column(Integer,nullable=False)     name = Column(VARCHAR(40))    SHack = Column(VARCHAR)    DB = Column(Integer)    Payments = Column(Integer)    Status = Column(VARCHAR)engine = create_engine('sqlite:///cdb.db')Base.Metadata.create_all(engine)file_name = 'clIEnt_db.csv'df = pd.read_csv(file_name)df.to_sql(con=engine,index_label='ID',name=cdb1.__tablename__,if_exists='replace')

解决方法 你熟悉Pandas Dataframe吗？

真的很简单(和调试)

pandas.read_csv(file_name)

In [5]: pandas.read_csv('/tmp/csvt.csv')Out[5]:            name SHack     DB  Payments         Status0  Loyiso Dwala  I156  13542        37  lightsOnly ON1  Attwell Fayo  I157  13077        32       lightsON2  DavID Mbhele   G25  13155        33       lightsON

要将DataFrames数据插入表中,只需使用pandas.DataFrame.to_sql即可

所以你的主要代码最终会看起来像这样：

engine = create_engine('sqlite:///cdb.db')Base.Metadata.create_all(engine)file_name = 'clIEnt_db.csv'df = pandas.read_csv(file_name)df.to_sql(con=engine,if_exists='replace')

您应该在我添加的文档链接中进一步阅读,并将函数Parameters设置为适合您的目的(特别是 – if_exists,index,index_label,dtype)

总结

以上是内存溢出为你收集整理的python – 使用sqlalchemy将CSV导入数据库全部内容，希望文章能够帮你解决python – 使用sqlalchemy将CSV导入数据库所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。