Я хотел бы использовать CSV файлы в базе данных
Используя sqlalchemy для загрузки csv файла в базу данных
Ответ 1
Из-за возможности SQLAlchemy я также использую его в проекте. Это происходит от объектно-ориентированного способа "говорить" с базой данных, а не с жестко кодированными операциями SQL, которые могут быть болью для управления. Не говоря уже, это также намного быстрее.
Чтобы ответить на ваш вопрос прямо, да! Хранение данных из CSV в базу данных с использованием SQLAlchemy - это кусок пирога. Вот полный рабочий пример (я использовал SQLAlchemy 1.0.6 и 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 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
(Примечание: это не обязательно "лучший" способ сделать это, но я думаю, что этот формат очень читабель для новичков, он также очень быстрый: 0.091s для 251 записей вставлены!)
Я думаю, что если вы пройдете через линию, вы увидите, какой ветер нужно использовать. Обратите внимание на отсутствие SQL-предложений - ура! Я также взял на себя смелость использовать numpy для загрузки содержимого CSV в две строки, но это можно сделать без него, если хотите.
Если вы хотите сравнить с традиционным способом его выполнения, здесь приведен полный рабочий пример:
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
(Примечание: даже в "старом" способе это отнюдь не лучший способ сделать это, но это очень читаемый и "1-к-1" перевод с пути SQLAlchemy по сравнению с "старым", способ.)
Обратите внимание на инструкции SQL: один для создания таблицы, другой - для вставки записей. Кроме того, обратите внимание, что для хранения длинных строк SQL в сравнении с простым добавлением атрибута класса немного более громоздко. Liking SQLAlchemy до сих пор?
Что касается вашего запроса на внешний ключ, конечно. SQLAlchemy имеет право сделать это тоже. Вот пример того, как будет выглядеть атрибут класса с присвоением внешнего ключа (если класс ForeignKey также был импортирован из модуля sqlalchemy):
class Asset_Analysis(Base):
#Tell SQLAlchemy what the table name is and if there 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.
Надеюсь, что это поможет!
Ответ 2
Если ваш CSV довольно большой, использование INSERTS очень неэффективно. Вы должны использовать механизмы массовой загрузки, которые отличаются от базы к основанию. Например. в PostgreSQL вы должны использовать метод "COPY FROM":
with open(csv_file_path, 'r') as f:
conn = create_engine('postgresql+psycopg2://...').raw_connection()
cursor = conn.cursor()
cmd = 'COPY tbl_name(col1, col2, col3) FROM STDIN WITH (FORMAT CSV, HEADER FALSE)'
cursor.copy_expert(cmd, f)
conn.commit()
Ответ 3
У меня была точно такая же проблема, и я обнаружил, что парадоксально проще использовать двухэтапный процесс с пандами:
import pandas as pd
with open(csv_file_path, 'r') as file:
data_df = pd.read_csv(file)
data_df.to_sql('tbl_name', con=engine, index=True, index_label='id', if_exists='replace')
Обратите внимание, что мой подход похож на этот, но каким-то образом Google отправил меня в эту ветку, поэтому я решил поделиться.
Ответ 4
Чтобы импортировать относительно небольшой файл CSV в базу данных с помощью sqlalchemy, вы можете использовать engine.execute(my_table.insert(), list_of_row_dicts), как подробно описано в разделе "Выполнение нескольких операторов" учебника по sqlalchemy.
Это иногда называется стилем вызова "executemany", потому что это приводит к вызову executemany DBAPI. Драйвер БД может выполнить один многозначный оператор INSERT .. VALUES (..), (..), (..), что приведет к меньшему количеству обращений к БД и более быстрому выполнению:
- коннектор MySQL делает это по умолчанию
- Психолог Postgres не делает not, если вы не инициализируете его с помощью create_engine (..., executemany_mode = 'values'))
Согласно sqlalchemy FAQ, это самый быстрый способ получить без использования методов массовой загрузки, специфичных для БД, таких как COPY FROM в Postgres, LOAD DATA LOCAL INFILE в MySQL и т.д. В частности, это быстрее, чем при использовании простого ORM (как в ответе @Manuel J. Diaz здесь), bulk_save_objects или bulk_insert_mappings.
import csv
from sqlalchemy import create_engine, Table, Column, Integer, MetaData
engine = create_engine('sqlite:///sqlalchemy.db', echo=True)
metadata = MetaData()
# Define the table with sqlalchemy:
my_table = Table('MyTable', metadata,
Column('foo', Integer),
Column('bar', Integer),
)
metadata.create_all(engine)
insert_query = my_table.insert()
# Or read the definition from the DB:
# metadata.reflect(engine, only=['MyTable'])
# my_table = Table('MyTable', metadata, autoload=True, autoload_with=engine)
# insert_query = my_table.insert()
# Or hardcode the SQL query:
# insert_query = "INSERT INTO MyTable (foo, bar) VALUES (:foo, :bar)"
with open('test.csv', 'r', encoding="utf-8") as csvfile:
csv_reader = csv.reader(csvfile, delimiter=',')
engine.execute(
insert_query,
[{"foo": row[0], "bar": row[1]}
for row in csv_reader]
)