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 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/b0404253-750e-499f-ad3c-9a26e844570a%40googlegroups.com.
