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/2c639533-fd1b-406d-8f0f-69fa6fff1188%40googlegroups.com.