I will reproduce this when I get done with work this evening and give the
specifics.
On Wednesday, October 28, 2020 at 7:46:26 AM UTC-5, Mike Bayer wrote:
>
>
>
> On Tue, Oct 27, 2020, at 11:56 PM, Andrew Martin wrote:
>
> 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?
>
>
> no, it's not expected, boolean True/False works on all backends without
> issue, assuming the target column is using the Boolean datatype at the
> SQLAlchemy level as well as that the actual column in the database is of
> the appropriate type, for PostgreSQL this would be BOOLEAN. when you say
> "throws errors" please share complete stack traces and complete error
> messages, thanks.
>
> can't do much more here without more specifics.
>
>
>
>
>
>
> 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] <javascript:>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/43b9c281-978a-4def-a279-f518201bd884o%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/43b9c281-978a-4def-a279-f518201bd884o%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
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/bae8f31f-8447-49e0-827c-30221c6b6af1o%40googlegroups.com.
