HI Ira, Again, that is an error that originates from inside the database layer and its telling you that one of the columns that you've queried on doesn't exist, which could mean a few things that will be hard for anyone to debug without access to the schema that you are trying to abstract upon. E.g., it could be that there is a column in civicrm_address that is called "postcode" or "postal_code" and so the name that we have defined on the Address class is simply wrong, e.g. Address.postalcode might need to be Address.postal_code, or something else. It is also possible that no such column actually exists in the civicrm_address table in the database, it might be defined on another table, or just not be there at all. Inspecting the result of 'SHOW CREATE TABLE civicrm_address' should provide you with enough detail to work out what is going on.
On Monday, 26 August 2019 10:57:14 UTC+10, Ira Fuchs wrote: > > Until I can get a new version of the server installed, I decided to try > running this scipt on my iPad using Pythonista. The script now looks like > this: > > from sqlalchemy import * > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import relationship > from sqlalchemy.orm import sessionmaker > from sqlalchemy import Column, Integer, String > import datetime > engine = create_engine('mysql+pymysql://root:pw!@ipaddr:3306/civicrm2') > Base = declarative_base() > Session = sessionmaker(bind=engine) > session = Session() > > class Contact(Base): > __tablename__ = "civicrm_contact" > id = Column(Integer, primary_key=True) > first_name = Column(String(64, u'utf8_unicode_ci'), index=True) > middle_name = Column(String(64, u'utf8_unicode_ci')) > last_name = Column(String(64, u'utf8_unicode_ci'), index=True) > display_name = Column(String(128, u'utf8_unicode_ci')) > > class Contribution(Base): > __tablename__ = 'civicrm_contribution' > > id = Column(INTEGER, primary_key=True) > contact_id = Column(ForeignKey(u'civicrm_contact.id', > ondelete=u'CASCADE'), nullable=False, index=True) > financial_type_id = Column(ForeignKey(u'civicrm_financial_type.id'), > index=True) > contribution_page_id = Column(ForeignKey(u' > civicrm_contribution_page.id', ondelete=u'SET NULL'), index=True) > payment_instrument_id = Column(INTEGER, index=True) > receive_date = Column(DateTime, index=True) > non_deductible_amount = Column(DECIMAL(20, 2), > server_default=text("'0.00'")) > total_amount = Column(DECIMAL(20, 2), nullable=False) > fee_amount = Column(DECIMAL(20, 2)) > net_amount = Column(DECIMAL(20, 2)) > trxn_id = Column(String(255, u'utf8_unicode_ci'), unique=True) > invoice_id = Column(String(255, u'utf8_unicode_ci')) > currency = Column(String(3, u'utf8_unicode_ci')) > cancel_date = Column(DateTime) > cancel_reason = Column(Text(collation=u'utf8_unicode_ci')) > receipt_date = Column(DateTime) > thankyou_date = Column(DateTime) > source = Column(String(255, u'utf8_unicode_ci'), index=True) > amount_level = Column(Text(collation=u'utf8_unicode_ci')) > contribution_recur_id = Column(ForeignKey(u' > civicrm_contribution_recur.id', ondelete=u'SET NULL'), index=True) > is_test = Column(Integer, server_default=text("'0'")) > is_pay_later = Column(Integer, server_default=text("'0'")) > contribution_status_id = Column(INTEGER, index=True) > address_id = Column(ForeignKey(u'civicrm_address.id', ondelete=u'SET > NULL'), index=True) > check_number = Column(String(255, u'utf8_unicode_ci')) > campaign_id = Column(ForeignKey(u'civicrm_campaign.id', > ondelete=u'SET NULL'), index=True) > tax_amount = Column(DECIMAL(20, 2)) > creditnote_id = Column(String(255, u'utf8_unicode_ci'), index=True) > revenue_recognition_date = Column(DateTime) > invoice_number = Column(String(255, u'utf8_unicode_ci')) > > address = relationship(u'CivicrmAddress') > contact = relationship(u'CivicrmContact') > class Address(Base): > __tablename__ = "civicrm_address" > id = Column(Integer, primary_key=True) > contact_id = Column(ForeignKey(u'civicrm_contact.id', > ondelete=u'CASCADE'), index=True) > street_address = Column(String(96, u'utf8_unicode_ci')) > city = Column(String(64, u'utf8_unicode_ci')) > postalcode = Column(String(64, u'utf8_unicode_ci')) > state_province_id = Column(String(64)) > country_id = Column(ForeignKey(u'civicrm_country.id', ondelete=u'SET > NULL')) > class Country(Base): > __tablename__ = "civicrm_country" > id = Column(Integer, primary_key=True) > name = Column(String(64, u'utf8_unicode_ci')) > class State(Base): > __tablename__ = "civicrm_state_province" > id = Column(Integer, primary_key=True) > name = Column(String(64, u'utf8_unicode_ci')) > abbreviation = Column(String(4, u'utf8_unicode_ci')) > country_id = Column(ForeignKey(u'civicrm_country.id')) > class Entity_Tag(Base): > __tablename__ = "civicrm_entity_tag" > id = Column(INTEGER, primary_key=True) > entity_id = Column(INTEGER, nullable=False, index=True) > tag_id = Column(ForeignKey(u'civicrm_tag.id', ondelete=u'CASCADE')) > > s = Session() > subquery = ( > s.query(Contact.display_name) > .filter( > Contribution.receive_date > datetime.date(2005, 7, 1), > Contribution.contact_id == Contact.id, > Contact.id == Entity_Tag.entity_id, > Entity_Tag.tag_id == 6, > ) > .subquery() > ) > result = ( > s.query( > Contact.last_name, > Contact.first_name, > Address.street_address, > Address.city, > Address.postalcode, > State.name, > Country.name.label("country"), > ) > .filter( > Contact.id == Entity_Tag.entity_id, > Entity_Tag.tag_id == 6, > Contact.id == Address.contact_id, > Address.state_province_id == State.id, > Address.country_id == Country.id, > Contact.display_name.notin_(subquery), > ) > .distinct() > .all() > ) > for row in result: > print(row) > > and the error Traceback is: > > Traceback (most recent call last): > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/sqlalchemy-test.py", > > line 108, in <module> > Contact.display_name.notin_(subquery), > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/orm/query.py", > > line 2588, in all > return list(self) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/orm/query.py", > > line 2736, in __iter__ > return self._execute_and_instances(context) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/orm/query.py", > > line 2751, in _execute_and_instances > result = conn.execute(querycontext.statement, self._params) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/base.py", > > line 914, in execute > return meth(self, multiparams, params) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/sql/elements.py", > > line 323, in _execute_on_connection > return connection._execute_clauseelement(self, multiparams, params) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/base.py", > > line 1010, in _execute_clauseelement > compiled_sql, distilled_params > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/base.py", > > line 1146, in _execute_context > context) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/base.py", > > line 1341, in _handle_dbapi_exception > exc_info > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/util/compat.py", > > line 200, in raise_from_cause > reraise(type(exception), exception, tb=exc_tb) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/base.py", > > line 1139, in _execute_context > context) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages/sqlalchemy/engine/default.py", > > line 450, in do_execute > cursor.execute(statement, parameters) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/cursors.py", > > line 170, in execute > result = self._query(query) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/cursors.py", > > line 328, in _query > conn.query(q) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/connections.py", > > line 517, in query > self._affected_rows = self._read_query_result(unbuffered=unbuffered) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/connections.py", > > line 732, in _read_query_result > result.read() > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/connections.py", > > line 1075, in read > first_packet = self.connection._read_packet() > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/connections.py", > > line 684, in _read_packet > packet.check_error() > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/protocol.py", > > line 220, in check_error > err.raise_mysql_exception(self._data) > File > "/private/var/mobile/Containers/Shared/AppGroup/05D14C00-CCAC-4E98-A3CC-96C6944B8534/Pythonista3/Documents/site-packages-2/pymysql/err.py", > > line 109, in raise_mysql_exception > raise errorclass(errno, errval) > InternalError: (pymysql.err.InternalError) (1054, u"Unknown column > 'civicrm_address.postalcode' in 'field list'") [SQL: u'SELECT DISTINCT > civicrm_contact.last_name AS civicrm_contact_last_name, > civicrm_contact.first_name AS civicrm_contact_first_name, > civicrm_address.street_address AS civicrm_address_street_address, > civicrm_address.city AS civicrm_address_city, civicrm_address.postalcode AS > civicrm_address_postalcode, civicrm_state_province.name AS > civicrm_state_province_name, civicrm_country.name AS country \nFROM > civicrm_contact, civicrm_address, civicrm_state_province, civicrm_country, > civicrm_entity_tag \nWHERE civicrm_contact.id = > civicrm_entity_tag.entity_id AND civicrm_entity_tag.tag_id = %(tag_id_1)s > AND civicrm_contact.id = civicrm_address.contact_id AND > civicrm_address.state_province_id = civicrm_state_province.id AND > civicrm_address.country_id = civicrm_country.id AND > civicrm_contact.display_name NOT IN (SELECT civicrm_contact.display_name > \nFROM civicrm_contribution \nWHERE civicrm_contribution.receive_date > > %(receive_date_1)s AND civicrm_contribution.contact_id = > civicrm_contact.id AND civicrm_contact.id = civicrm_entity_tag.entity_id > AND civicrm_entity_tag.tag_id = %(tag_id_2)s)'] [parameters: > {u'receive_date_1': datetime.date(2005, 7, 1), u'tag_id_1': 6, u'tag_id_2': > 6}] > > > On Thursday, August 22, 2019 at 9:51:22 PM UTC-4, Ira Fuchs wrote: >> >> Yes, that would appear to be the problem. When I was in 2.7 it worked and >> in 3.6 the version of pymysql requires a later version (>5.5) of the >> server. I am not able to upgrade the server at this point so I need to >> figure out how to get my notebook back to Python 2 for the time being. >> >> On Aug 22, 2019, at 9:37 PM, Peter Schutt <peter....@gmail.com >> <javascript:>> wrote: >> >> Some time between yesterday and today you have switched python >> interpreters between 2.7 and 3.6. Yesterday your errors were originating >> from modules located in "/Users/ihf/anaconda2/lib/python2.7/", today they >> seem to be coming from "~/anaconda2/lib/python3.6/". To be honest, it's >> better if you are using python 3.x as 2.7 goes end of life in only a few >> short months. This would explain why you had to reinstall pymysql as you >> are now working in a totally different environment. >> >> The last error you've shown originates from the database layer. What >> version of mysql are you using and might that have changed along with your >> environment? utf8mb4 was introduced in 5.5.3, read more here: >> https://stackoverflow.com/questions/21911733/error-1115-42000-unknown-character-set-utf8mb4 >> . >> >> On Friday, 23 August 2019 11:05:14 UTC+10, Ira Fuchs wrote: >>> >>> OK, I made a few changes/corrections to the Class definitions: >>> >>> class Contact(Base): >>> __tablename__ = "civicrm_contact" >>> id = Column(Integer, primary_key=True) >>> first_name = Column(String(64, u'utf8_unicode_ci'), index=True) >>> middle_name = Column(String(64, u'utf8_unicode_ci')) >>> last_name = Column(String(64, u'utf8_unicode_ci'), index=True) >>> display_name = Column(String(128, u'utf8_unicode_ci')) >>> >>> class Contribution(Base): >>> __tablename__ = 'civicrm_contribution' >>> >>> id = Column(INTEGER, primary_key=True, comment=u'Contribution ID') >>> contact_id = Column(ForeignKey(u'civicrm_contact.id', >>> ondelete=u'CASCADE'), nullable=False, index=True) >>> financial_type_id = Column(ForeignKey(u'civicrm_financial_type.id'), >>> index=True) >>> contribution_page_id = Column(ForeignKey(u' >>> civicrm_contribution_page.id', ondelete=u'SET NULL'), index=True) >>> payment_instrument_id = Column(INTEGER, index=True, comment=u'FK to >>> Payment Instrument') >>> receive_date = Column(DateTime, index=True, comment=u'when was gift >>> received') >>> non_deductible_amount = Column(DECIMAL(20, 2), >>> server_default=text("'0.00'")) >>> total_amount = Column(DECIMAL(20, 2), nullable=False) >>> fee_amount = Column(DECIMAL(20, 2), comment=u'actual processor fee >>> if known - may be 0.') >>> net_amount = Column(DECIMAL(20, 2)) >>> trxn_id = Column(String(255, u'utf8_unicode_ci'), unique=True) >>> invoice_id = Column(String(255, u'utf8_unicode_ci')) >>> currency = Column(String(3, u'utf8_unicode_ci')) >>> cancel_date = Column(DateTime, comment=u'when was gift cancelled') >>> cancel_reason = Column(Text(collation=u'utf8_unicode_ci')) >>> receipt_date = Column(DateTime) >>> thankyou_date = Column(DateTime, comment=u'when (if) was donor >>> thanked') >>> source = Column(String(255, u'utf8_unicode_ci'), index=True, >>> comment=u'Origin of this Contribution.') >>> amount_level = Column(Text(collation=u'utf8_unicode_ci')) >>> contribution_recur_id = Column(ForeignKey(u' >>> civicrm_contribution_recur.id', ondelete=u'SET NULL'), index=True) >>> is_test = Column(Integer, server_default=text("'0'")) >>> is_pay_later = Column(Integer, server_default=text("'0'")) >>> contribution_status_id = Column(INTEGER, index=True) >>> address_id = Column(ForeignKey(u'civicrm_address.id', >>> ondelete=u'SET NULL'), index=True) >>> check_number = Column(String(255, u'utf8_unicode_ci')) >>> campaign_id = Column(ForeignKey(u'civicrm_campaign.id', >>> ondelete=u'SET NULL'), index=True) >>> tax_amount = Column(DECIMAL(20, 2), comment=u'Total tax amount of >>> this contribution.') >>> creditnote_id = Column(String(255, u'utf8_unicode_ci'), index=True) >>> revenue_recognition_date = Column(DateTime, comment=u'Stores the >>> date when revenue should be recognized.') >>> invoice_number = Column(String(255, u'utf8_unicode_ci'), >>> comment=u'Human readable invoice number') >>> >>> address = relationship(u'CivicrmAddress') >>> contact = relationship(u'CivicrmContact') >>> class Address(Base): >>> __tablename__ = "civicrm_address" >>> id = Column(Integer, primary_key=True) >>> contact_id = Column(ForeignKey(u'civicrm_contact.id', >>> ondelete=u'CASCADE'), index=True) >>> street_address = Column(String(96, u'utf8_unicode_ci')) >>> city = Column(String(64, u'utf8_unicode_ci')) >>> postalcode = Column(String(64, u'utf8_unicode_ci')) >>> state_province_id = Column(String(64)) >>> country_id = Column(ForeignKey(u'civicrm_country.id', >>> ondelete=u'SET NULL')) >>> class Country(Base): >>> __tablename__ = "civicrm_country" >>> id = Column(Integer, primary_key=True) >>> name = Column(String(64, u'utf8_unicode_ci')) >>> class State(Base): >>> __tablename__ = "civicrm_state_province" >>> id = Column(Integer, primary_key=True) >>> name = Column(String(64, u'utf8_unicode_ci')) >>> abbreviation = Column(String(4, u'utf8_unicode_ci')) >>> country_id = Column(ForeignKey(u'civicrm_country.id')) >>> class Entity_Tag(Base): >>> __tablename__ = "civicrm_entity_tag" >>> id = Column(INTEGER, primary_key=True) >>> entity_id = Column(INTEGER, nullable=False, index=True) >>> tag_id = Column(ForeignKey(u'civicrm_tag.id', ondelete=u'CASCADE')) >>> >>> then I created a session and ran your query (with one or two corrections: >>> >>> s = Session() >>> subquery = ( >>> s.query(Contact.display_name) >>> .filter( >>> Contribution.receive_date > datetime.date(2005, 7, 1), >>> Contribution.contact_id == Contact.id, >>> Contact.id == Entity_Tag.entity_id, >>> Entity_Tag.tag_id == 6, >>> ) >>> .subquery() >>> ) >>> result = ( >>> s.query( >>> Contact.last_name, >>> Contact.first_name, >>> Address.street_address, >>> Address.city, >>> Address.postalcode, >>> State.name, >>> Country.name.label("country"), >>> ) >>> .filter( >>> Contact.id == Entity_Tag.entity_id, >>> Entity_Tag.tag_id == 6, >>> Contact.id == Address.contact_id, >>> Address.state_province_id == State.id, >>> Address.country_id == Country.id, >>> Contact.display_name.notin_(subquery), >>> ) >>> .distinct() >>> .all() >>> ) >>> >>> and the result is the same error as when I just try to execute an SQL >>> statement without using ORM(I'm note including the entire traceback unless >>> you need it): >>> >>> InternalError: (pymysql.err.InternalError) (1115, "Unknown character set: >>> 'utf8mb4'") >>> >>> Since this worked before, all I can think is that I somehow updated or >>> changed pymysql and the result is this error. >>> >>> >>> >>> >>> >>> >> -- >> 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 a topic in the >> Google Groups "sqlalchemy" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sqlalchemy/xtp9Lz4VdBI/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> sqlal...@googlegroups.com <javascript:>. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sqlalchemy/df9aa766-90d3-440b-8b48-18bfc47f568f%40googlegroups.com >> >> <https://groups.google.com/d/msgid/sqlalchemy/df9aa766-90d3-440b-8b48-18bfc47f568f%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/5b95fcee-c1ea-4ff1-88f0-0a0d762834cc%40googlegroups.com.