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 > > <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 > <http://civicrm_contact.id/>', ondelete=u'CASCADE'), nullable=False, > index=True) > financial_type_id = Column(ForeignKey(u'civicrm_financial_type.id > <http://civicrm_financial_type.id/>'), index=True) > contribution_page_id = Column(ForeignKey(u'civicrm_contribution_page.id > <http://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 > <http://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 > <http://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 > <http://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 > <http://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 > <http://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 > <http://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 <http://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/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <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 > <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 > <mailto: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/22184F28-F05B-4A19-B37E-2A5335BE4EA9%40gmail.com.
