Good to hear and you're welcome:)
On Monday, 26 August 2019 13:00:02 UTC+10, Ira Fuchs wrote:
>
> 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
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,
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
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
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
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
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'))
The line causing the error now is:
with engine.connect() as con:
rs = con.execute('SELECT DISTINCT last_name,
first_name,addressee_display, street_address, city, a.name, postal_code,
f.name as country FROM civicrm_contact c, civicrm_entity_tag d ,
civicrm_address e, civicrm_state_province
Weird. When I go back to jsut running the SQL query, I now get:
InternalError: (pymysql.err.InternalError) (1115, "Unknown character set:
'utf8mb4'")
(Background on this error at: http://sqlalche.me/e/2j85)
I seem to be regressing.
On Thursday, August 22, 2019 at 6:53:46 PM UTC-4, Ira Fuchs
Strange thing just occurred where I now get an error message that pymysql
module is now missing. I redid a pip install and it seems to get passed
that point and comes back with a new error. This was just running the SQL
query to make sure I could do that. I commented it out of the notebook for
The `ondelete=...` keyword argument is a parameter to the `ForeignKey()`
constructor, not the `Column`.
This:
contact_id = Column(ForeignKey(u'civicrm_contact.id'), ondelete=u'CASCADE',
nullable=False, index=True)
Should be this:
contact_id = Column(ForeignKey(u'civicrm_contact.id',
I fixed the syntax errors and tried your rewritten query but I got an
error in the definitions:
TypeError Traceback (most recent call
last) in () 7 display_name =
Column(String(128, u'utf8_unicode_ci')) 8 > 9 class
CivicrmContribution(Base):
A couple of typos found re-reading my post, the Contribution.contact_id
with a foreign key definition should be `contact_id = Column(Integer,
ForeignKey('civicrm_contact'), nullable=False)`, I left out the closing
parenthesis to the ForeignKey constructor.
Also, the very last line, I didn't
Hi Ira,
For example Integer(xx) says that Integer cannot have parameters and
> Tinyint seems not to exist.
I'm aware of sqlacodegen, although never had the need to use it myself.
Those issues sound to me like it is using the mysql dialect types, not the
standard sqlalchemy types. For example
I noticed that some of the definitions created by sqlacodegen are resulting
in errors in python. For example Integer(xx) says that Integer cannot have
parameters and Tinyint seems not to exist. Perhaps this is a result of my
running a less than current version of mysql?
On Tuesday, August 20,
Just an aside: I discovered the sqlacodegen tool which will create the
sqlalchemy class definitions automatically. I ran it against the civicrm
mysql db and it worked. The definitions comprise 5881 lines (428KB).
Fortunately I don't need much of it for my purposes.
On Tuesday, August 20, 2019
I think I may have all the definitions:
class Contact(Base):
__tablename__ = "civicrm_contact"
id = Column(Integer, primary_key=True)
last_name = Column(String(20), nullable=False)
first_name = Column(String(20), nullable=False)
class Contribution(Base):
__tablename__ =
OK, fixed the case:
class Contact(Base):
__tablename__ = "civicrm_contact"
id = Column(Integer, primary_key=True)
last_name = Column(String(20), nullable=False)
first_name = Column(String(20), nullable=False)
class Contribution(Base):
__tablename__ = "civicrm_contribution"
I tried to extend the model a bit further but I guess Datetime is specified
another way?
class Contribution(Base):
__tablename__ = "civicrm_contribution"
id = Column(Integer, primary_key=True)
contact_id = Column(Integer, nullable=False)
receive_date =
Cool, how about we walk through creating a model for your schema as an
example. As we go I can point you to the relevant sections of the
tutorials/docs as they are great and will explain the details much better
than I can, and I'll try to address any questions you have along the way.
The first
On 18Aug2019 19:36, Ira Fuchs wrote:
Thanks for your reply and offer to help. I am able to create an Engine and
connect to the MySQL db. I can execute simple sql queries although I wasn't
able to get the query I posted to work due to a syntax error (probably
having to do with the quotes). I
Thanks for your reply and offer to help. I am able to create an Engine and
connect to the MySQL db. I can execute simple sql queries although I wasn't
able to get the query I posted to work due to a syntax error (probably
having to do with the quotes). I have not mapped any tables to classes.
Hi Ira, I'd be happy to help you find your feet with the SQLAlchemy ORM.
In general when creating an application that uses the SQLAlchemy ORM, you
would start with an Engine (for connecting to the db), a declarative base
class (maps db table to python class) and a Session instance (for using a
23 matches
Mail list logo
