This is probably a weirdly specific question, but I have a workflow that
involves loading lots of CSVs into Postgres. Some of them are very large,
so I want to cut overhead and not use CSV Dictreader. Wanted to use named
tuples instead.
Here's a header and a line from a file:
date_dim_id,date_actual,epoch,day_suffix,day_name,day_of_week,day_of_month,
day_of_quarter,day_of_year,week_of_month,week_of_year,week_of_year_iso,
month_actual,month_name,month_name_abbreviated,quarter_actual,quarter_name,
year_actual,first_day_of_week,last_day_of_week,first_day_of_month,
last_day_of_month,first_day_of_quarter,last_day_of_quarter,first_day_of_year
,last_day_of_year,mmyyyy,mmddyyyy,weekend_indr
Here's a line:
20150101,1/1/15,1420070400,1st,Thursday ,4,1,1,1,1,1,2015-W01-4,1,January
,Jan,1,First,2015,12/29/14,1/4/15,1/1/15,1/31/15,1/1/15,3/31/15,1/1/15,12/31/15,12015,1012015,0
The idea was a generic file loader that takes a filename and a class and
then loads it. Here's the class:
class DimDate(Base):
__tablename__ = "dim_date"
date_dim_id = Column(Integer, primary_key=True)
date_actual = Column(Date, nullable=False, index=True)
epoch = Column(BigInteger, nullable=False)
day_suffix = Column(String(4), nullable=False)
day_name = Column(String(9), nullable=False)
day_of_week = Column(Integer, nullable=False)
day_of_month = Column(Integer, nullable=False)
day_of_quarter = Column(Integer, nullable=False)
day_of_year = Column(Integer, nullable=False)
week_of_month = Column(Integer, nullable=False)
week_of_year = Column(Integer, nullable=False)
week_of_year_iso = Column(CHAR(10), nullable=False)
month_actual = Column(Integer, nullable=False)
month_name = Column(String(9), nullable=False)
month_name_abbreviated = Column(CHAR(3), nullable=False)
quarter_actual = Column(Integer, nullable=False)
quarter_name = Column(String(9), nullable=False)
year_actual = Column(Integer, nullable=False)
first_day_of_week = Column(Date, nullable=False)
last_day_of_week = Column(Date, nullable=False)
first_day_of_month = Column(Date, nullable=False)
last_day_of_month = Column(Date, nullable=False)
first_day_of_quarter = Column(Date, nullable=False)
last_day_of_quarter = Column(Date, nullable=False)
first_day_of_year = Column(Date, nullable=False)
last_day_of_year = Column(Date, nullable=False)
mmyyyy = Column(CHAR(6), nullable=False)
mmddyyyy = Column(CHAR(10), nullable=False)
weekend_indr = Column(Boolean, nullable=False)
What I ran into is a couple of things. 1. Python True/False throws errors
when writing to a Boolean. 2. You seem to have to int() a 1 or 0 to avoid a
dbapi error.
Is this expected? And does it have more to do with Postgres than it does
SQLA?
Here's the code I came up with for generic file loader that actually works.
I'm sure it's garbage, and I'd like to refactor it, but it works.
def load_file(file_name, cls):
"""assumes that csv file has headers and that headers match the
names of the columns for a given sqla class. Also assumes that the
sqla class is inheriting from meta.Base"""
inst = inspect(cls)
# attr_names = sorted([c_attr.key for c_attr in
inst.mapper.column_attrs])
attr_vals = sorted([(c.name, c.type) for c in inst.c])
s = get_sqla_session()
with open(file_name, newline="") as infile:
reader = csv.reader(infile)
Data = namedtuple("Data", next(reader))
tuple_fields = Data._fields
for data in map(Data._make, [(c.strip() for c in row) for row in
reader]):
new_obj = cls()
for attr in attr_vals:
for field in tuple_fields:
if attr[0] == field:
if str(attr[1]) == "BOOLEAN":
setattr(new_obj, field, int(getattr(data, field
)))
else:
# print(data, attr[1])
setattr(new_obj, field, getattr(data, field))
new_obj.id = IDService.create_snowflake_id()
new_obj.is_deleted = 0
s.add(new_obj)
s.commit()
This isn't really a big deal, and maybe not even appropriate, but I had an
absolute shit 12 hours trying to figure out why SQLA booleans are such a
hassle.
If anyone has thoughts, I'm happy to hear them.
Thank you!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/43b9c281-978a-4def-a279-f518201bd884o%40googlegroups.com.
