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.m.sch...@gmail.com> > 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 > sqlalchemy+unsubscr...@googlegroups.com. > 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/7293b0c9-50c3-40be-9404-068d04980284%40googlegroups.com.