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/7293b0c9-50c3-40be-9404-068d04980284%40googlegroups.com.
