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 <[email protected]> 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
>>> [email protected].
>>> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/2c639533-fd1b-406d-8f0f-69fa6fff1188%40googlegroups.com.
