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]
>> <javascript:>> 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] <javascript:>.
>> 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/5b95fcee-c1ea-4ff1-88f0-0a0d762834cc%40googlegroups.com.
