使用sqlalchemy将csv文件加载到数据库中 [英] using sqlalchemy to load csv file into a database
问题描述
我想在数据库中使用csv文件
I would like to use csv files in to a database
推荐答案
由于SQLAlchemy的强大功能，我也在项目上使用它。它的强大功能来自于与数据库对话的面向对象方式，而不是对可能难以管理的SQL语句进行硬编码。更不用说了，它的速度也快很多。
Because of the power of SQLAlchemy, I'm also using it on a project. It's power comes from the object-oriented way of "talking" to a database instead of hardcoding SQL statements that can be a pain to manage. Not to mention, it's also a lot faster.
坦率地回答您的问题，是的！使用SQLAlchemy将数据从CSV存储到数据库中简直是小菜一碟。这是一个完整的工作示例（我使用了SQLAlchemy 1.0.6和Python 2.7.6）：
To answer your question bluntly, yes! Storing data from a CSV into a database using SQLAlchemy is a piece of cake. Here's a full working example (I used SQLAlchemy 1.0.6 and Python 2.7.6):
from numpy import genfromtxt
from time import time
from datetime import datetime
from sqlalchemy import Column, Integer, Float, Date
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
def Load_Data(file_name):
data = genfromtxt(file_name, delimiter=',', skip_header=1, converters={0: lambda s: str(s)})
return data.tolist()
Base = declarative_base()
class Price_History(Base):
#Tell SQLAlchemy what the table name is and if there's any table-specific arguments it should know about
__tablename__ = 'Price_History'
__table_args__ = {'sqlite_autoincrement': True}
#tell SQLAlchemy the name of column and its attributes:
id = Column(Integer, primary_key=True, nullable=False)
date = Column(Date)
opn = Column(Float)
hi = Column(Float)
lo = Column(Float)
close = Column(Float)
vol = Column(Float)
if __name__ == "__main__":
t = time()
#Create the database
engine = create_engine('sqlite:///csv_test.db')
Base.metadata.create_all(engine)
#Create the session
session = sessionmaker()
session.configure(bind=engine)
s = session()
try:
file_name = "t.csv" #sample CSV file used: http://www.google.com/finance/historical?q=NYSE%3AT&ei=W4ikVam8LYWjmAGjhoHACw&output=csv
data = Load_Data(file_name)
for i in data:
record = Price_History(**{
'date' : datetime.strptime(i[0], '%d-%b-%y').date(),
'opn' : i[1],
'hi' : i[2],
'lo' : i[3],
'close' : i[4],
'vol' : i[5]
})
s.add(record) #Add all the records
s.commit() #Attempt to commit all the records
except:
s.rollback() #Rollback the changes on error
finally:
s.close() #Close the connection
print "Time elapsed: " + str(time() - t) + " s." #0.091s
（注意：这不一定是最佳方法，但是我认为这种格式对于初学者来说可读性很强；它也非常快：插入251条记录时为0.091秒！）
(Note: this is not necessarily the "best" way to do this, but I think this format is very readable for a beginner; it's also very fast: 0.091s for 251 records inserted!)
我想如果您逐行浏览它，您会发现会看到使用起来很轻松。注意缺少SQL语句-哎呀！我还随意使用numpy在两行中加载CSV内容，但是如果愿意，也可以不使用它。
I think if you go through it line by line, you'll see what a breeze it is to use. Notice the lack of SQL statements -- hooray! I also took the liberty of using numpy to load the CSV contents in two lines, but it can be done without it if you like.
如果要与传统方式，下面是一个完整的示例供您参考：
If you wanted to compare against the traditional way of doing it, here's a full-working example for reference:
import sqlite3
import time
from numpy import genfromtxt
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
def Create_DB(db):
#Create DB and format it as needed
with sqlite3.connect(db) as conn:
conn.row_factory = dict_factory
conn.text_factory = str
cursor = conn.cursor()
cursor.execute("CREATE TABLE [Price_History] ([id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, [date] DATE, [opn] FLOAT, [hi] FLOAT, [lo] FLOAT, [close] FLOAT, [vol] INTEGER);")
def Add_Record(db, data):
#Insert record into table
with sqlite3.connect(db) as conn:
conn.row_factory = dict_factory
conn.text_factory = str
cursor = conn.cursor()
cursor.execute("INSERT INTO Price_History({cols}) VALUES({vals});".format(cols = str(data.keys()).strip('[]'),
vals=str([data[i] for i in data]).strip('[]')
))
def Load_Data(file_name):
data = genfromtxt(file_name, delimiter=',', skiprows=1, converters={0: lambda s: str(s)})
return data.tolist()
if __name__ == "__main__":
t = time.time()
db = 'csv_test_sql.db' #Database filename
file_name = "t.csv" #sample CSV file used: http://www.google.com/finance/historical?q=NYSE%3AT&ei=W4ikVam8LYWjmAGjhoHACw&output=csv
data = Load_Data(file_name) #Get data from CSV
Create_DB(db) #Create DB
#For every record, format and insert to table
for i in data:
record = {
'date' : i[0],
'opn' : i[1],
'hi' : i[2],
'lo' : i[3],
'close' : i[4],
'vol' : i[5]
}
Add_Record(db, record)
print "Time elapsed: " + str(time.time() - t) + " s." #3.604s
（注意：即使采用旧方式，也绝不是最好的方法这样做的方法，但是它非常易读，并且是SQLAlchemy方法与旧方法的一对一转换。）
(Note: even in the "old" way, this is by no means the best way to do this, but it's very readable and a "1-to-1" translation from the SQLAlchemy way vs. the "old" way.)
请注意SQL语句：一个创建表，另一个插入记录。另外，请注意，与添加简单的类属性相比，维护长的SQL字符串要麻烦一些。
Notice the the SQL statements: one to create the table, the other to insert records. Also, notice that it's a bit more cumbersome to maintain long SQL strings vs. a simple class attribute addition. Liking SQLAlchemy so far?
当然，关于您的外键查询。 SQLAlchemy也具有执行此操作的能力。这是一个使用外键分配的类属性的示例（假设 ForeignKey 类也已从 sqlalchemy 模块）：
As for your foreign key inquiry, of course. SQLAlchemy has the power to do this too. Here's an example of how a class attribute would look like with a foreign key assignment (assuming the ForeignKey class has also been imported from the sqlalchemy module):
class Asset_Analysis(Base):
#Tell SQLAlchemy what the table name is and if there's any table-specific arguments it should know about
__tablename__ = 'Asset_Analysis'
__table_args__ = {'sqlite_autoincrement': True}
#tell SQLAlchemy the name of column and its attributes:
id = Column(Integer, primary_key=True, nullable=False)
fid = Column(Integer, ForeignKey('Price_History.id'))
会将 fid列指向Price_History的id列的外键。
which points the "fid" column as a foreign key to Price_History's id column.
希望如此！
这篇关于使用sqlalchemy将csv文件加载到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
