Good to hear and you're welcome:) On Monday, 26 August 2019 13:00:02 UTC+10, Ira Fuchs wrote: > > That's it. (postal_code). The ORM query now works from the iPad! Now I > need to sort things out with Python on the Mac and with MySQL server. > Having a working example like this helps as I go through the documentation. > Thanks very much much for your patient assistance. > > On Sunday, August 25, 2019 at 9:35:28 PM UTC-4, Peter Schutt wrote: >> >> 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> 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. >>>> 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/ab6158b0-cc57-471f-b2a0-380b743282c1%40googlegroups.com.