OK please include: database backend, version
database driver (DBAPI) in use, version use of special libraries, int() called on 0 or 1 suggests these are not Python integers, please specify any numpy or pandas use and read https://docs.sqlalchemy.org/en/13/faq/thirdparty.html python version etc On Wed, Oct 28, 2020, at 10:33 AM, Andrew Martin wrote: > 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 sqlal...@googlegroups.com. >>> 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/bae8f31f-8447-49e0-827c-30221c6b6af1o%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/bae8f31f-8447-49e0-827c-30221c6b6af1o%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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/efc78324-6c14-4c08-91b8-0667d6cd8816%40www.fastmail.com.
