Translating sql query to sqlalchemy

[Ira Fuchs]

I am new to sqlalchemy and I would like to begin using it to create scripts with some sql queries that I have been using but need to change from time to time. It would help a lot if someone could translate one of these queries to python sqlalchemy using an ORM (not simply executing the query as is) so that I could use this as a template for other queries, or at least learn from it.

One of my queries looks like this:

SELECT DISTINCT last_name, first_name, street_address, city, a.name, postal_code, f.name as country FROM db_contact c, db_entity_tag d , db_address e, db_state_province a, db_country f WHERE c.id = d.entity_id and tag_id = 6 and c.id = e.contact_id AND state_province_id = a.id  and e.country_id = f.id and  display_name not in ( SELECT display_name FROM db_contribution, db_contact c, db_entity_tag d WHERE receive_date > '2005-07-01' and contact_id = c.id and c.id = entity_id and tag_id = 6 )

Thanks for any help.

[Peter Schutt]

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 connection to issue queries). Do you have any familiarity with those concepts?

[Ira Fuchs]

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.

[Cameron Simpson]

On 18Aug2019 19:36, Ira Fuchs <iraf...@gmail.com> 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 have not mapped any tables to classes.

Perhaps you could post your current query code and the error message.

Cheers,
Cameron Simpson <c...@cskk.id.au>

[Peter Schutt]

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 thing you need is your Base class, all of your ORM models will inherit from this:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Your models need to inherit from Base, need a __tablename__ class attribute and at least a primary key column. For example, a class to represent your "db_contact" table might look like this:

from sqlalchemy import Column, Integer, String

class Contact(Base):

    __tablename__ = "db_contact"

    id = Column(Integer, primary_key=True)
    last_name = Column(String(20), nullable=False)
    first_name = Column(String(20), nullable=False)

Some things to note this far along:

• In this example, the name of the Column in the database is inferred from the name of the class attribute that the Column is assigned to.
• Column constructors take arguments that define specifics of how the column is defined in the database. E.g. `primary_key=True`, `nullable=False`. By declaring a single integer column as primary key, it will automatically be specified as autoincrement in the schema, as well as the primary key. Setting nullable=False on other columns declares that a NOT NULL constraint should be set on those columns.
• As you are using MySQL, String types (which represent VARCHAR type in the database) require a length. If you were creating models for other backends such as SQLite or postgres, then that isn't required.

Nothing that I've covered here isn't covered in the early stages of the ORM Tutorial. Also, you can read the Column API to get a feel for the arguments that you can pass to Column in order to define your schema.

One more important element is the Foreign Key. I'll use your "db_entity_tag" table to give you an example of that:

from sqlalchemy import ForeignKey

class EntityTag(Base):

    __tablename__ = "db_entity_tag"

    id = Column(Integer, primary_key=True)
    entity_id = Column(
        Integer,
        ForeignKey('db_contact.id', ondelete="CASCADE"),
        nullable=False,
    )

Pass the name of the related column to the ForeignKey object (note that this isn't "Class.attributename", it is "tablename.columnname") and pass the ForeignKey object as a positional argument to the Column constructor _after_ the column type argument. ForeignKey api is documented here and touched on in the orm tutorial here.

Have a go at filling in the columns that I've missed in those models above and defining the rest of the tables in your schema and once you get that done we can move on. Happy to try to answer any questions you might have along the way.

[Ira Fuchs]

OK, I made some progress (see screenshot).  I don't need the full model to be represented as I will not need many of the columns in each of the tables. If I could flesh this out enough so that I can do the query posted above I can probably use that as a template to move forward.

[Ira Fuchs]

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"
    id = Column(Integer, primary_key=True)
    contact_id = Column(Integer, nullable=False)
    receive_date = Column(DateTime,nullable=False)
    total_amount = Column(DECIMAL(precision=20,scale=2), nullable=False)

[Ira Fuchs]

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__ = "civicrm_contribution"
    id = Column(Integer, primary_key=True)
    contact_id = Column(Integer, nullable=False)
    receive_date = Column(DateTime,nullable=False)
    total_amount = Column(DECIMAL(precision=20,scale=2), nullable=False)

class Address(Base):
    __tablename__ = "civicrm_address"

    id = Column(Integer, primary_key=True)
    contact_id = Column(Integer, nullable=False)

    street_address = Column(String(96), nullable=False)
    city = Column(String(64), nullable=False)
    postalcode = Column(String(64), nullable=False)
    country_id = Column(Integer, nullable=False)
class Country(Base):
    __tablename__ = "civicrm_country"
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
class State(Base):
    __tablename__ = "civicrm_state_province"
    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False)
    abbreviation = Column(String(4), nullable=False)
    country_id = Column(Integer, nullable=False)
class Entity_Tag(Base):
    __tablename__ = "civicrm_entity_tag"
    id = Column(Integer, primary_key=True)
    entity_id = Column(Integer, nullable=False)
    tag_id = Column(Integer, nullable=False)

Now, how to create the equivalent SQL query?  I presume that this front-end overhead will pay back when I write more interesting scripts that use data from Civicrm. If this works then I can map the remainder of the (many) tables.

[Ira Fuchs]

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.

[Ira Fuchs]

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?

[Peter Schutt]

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 `from sqlalchemy import Integer` will not accept an argument but `from sqlalchemy.dialects.mysql import INTEGER` does accept display_width as a parameter amongst others. Also, `from sqlalchemy import TinyInteger` will raise an import error but `from sqlalchemy.dialects.mysql import TINYINT` works.

I presume that this front-end overhead will pay back when I write more interesting scripts that use data from Civicrm. If this works then I can map the remainder of the (many) tables.

Well, that's certainly the idea. The abstraction allows for writing code that is both easier to write and read. Essentially you write a Python app and let sqlalchemy handle the bridge between your application and the database. It's not a magic bullet, there are plenty of sql paradigms still exposed to you through the ORM and there are limitations to using it, primarily performance relative to doing bulk operations (for example, see here). Like any tool, you need to weigh up the pros and cons. For example, if you are just using the ORM in order to generate schema or construct raw queries, there are better ways. The benefit of the ORM comes once you start understanding and using the richer feature set. For example, relationships.

In the models that you built above, I notice that you haven't defined any foreign keys on columns. I understand that your schema is already created and so the foreign keys are already defined at the database level, but that information is useful to sqlalchemy when you want to create relationships and explicit joins in your queries. One example of applying a foreign key to your columns would be on the `Contribution.contact_id` column. Defining that as a foreign key would be as simple as changing the definition to `contact_id = Column(Integer, ForeignKey('civicrm_contact', nullable=False)`. This would then allow you to define a relationship attribute on your Contribution model, e.g. `contact = relationship('Contact')`. That allows you to access the instance that represents the contact associated with a given contribution through instance attribute access, e.g. `contribution_instance.contact` would return an instance of `Contact` and sqlalchemy will issue the necessary queries behind the scenes to make that happen.

Now, how to create the equivalent SQL query?

Well, I've had to make a couple of guesses to fill in a couple of blanks. I assumed that the column in your query `state_province_id` should be an attribute on the `Address` model, and that the `display_name` column belongs to the `Entity_Tag` model, as neither of those fields are defined elsewhere. We haven't spoken about the Session in any detail yet either, but you can read https://docs.sqlalchemy.org/en/13/orm/tutorial.html#creating-a-session if you need. I've purely tried to emulate your original query as closely as possible.

    s = Session()

    subquery = (

        s.query(Entity_Tag.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,

            Entity_Tag.display_name.notin_(subquery),

        )

        .distinct()

        .all()

    )

This query issues this sql:

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
FROM civicrm_contact, civicrm_address, civicrm_state_province, civicrm_country, civicrm_entity_tag
WHERE 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_entity_tag.display_name NOT IN (SELECT civicrm_entity_tag.display_name
FROM civicrm_contribution
WHERE 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)

Notice that the `FROM` clause in the subquery only contains one column, this is becuase the other columns have been automatically correlated with the columns expressed in the enclosing query. See here: https://docs.sqlalchemy.org/en/13/core/tutorial.html#correlated-subqueries.

The scalar values in the query that have been substituted with placeholders (e.g. `%(tag_id_2)s` are passed to the db driver in dictionary form along with the query, e.g. `{'tag_id_1': 6, 'receive_date_1': datetime.date(2005, 7, 1), 'tag_id_2': 6}`.

[Peter Schutt]

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 close the parenthesis for the placeholders example, should read:  "(e.g. `%(tag_id_2)s`) are passed...".

Couldn't find a way to edit the original.

[Ira Fuchs]

I fixed the syntax errors and tried your rewritten query but I  got an error in the definitions:

TypeError                                 Traceback (most recent call last)
<ipython-input-6-97e6a9952682> in <module>()
      7     display_name = Column(String(128, u'utf8_unicode_ci'))
      8 
----> 9 class CivicrmContribution(Base):
     10     __tablename__ = 'civicrm_contribution'
     11 

<ipython-input-6-97e6a9952682> in CivicrmContribution()
     11 
     12     id = Column(INTEGER, primary_key=True, comment=u'Contribution ID')
---> 13     contact_id = Column(ForeignKey(u'civicrm_contact.id'), ondelete=u'CASCADE', nullable=False, index=True)
     14     financial_type_id = Column(ForeignKey(u'civicrm_financial_type.id'), index=True)
     15     contribution_page_id = Column(ForeignKey(u'civicrm_contribution_page.id', ondelete=u'SET NULL'), index=True)

/Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/schema.pyc in __init__(self, *args, **kwargs)
   1382             self.info = kwargs.pop("info")
   1383 
-> 1384         self._extra_kwargs(**kwargs)
   1385 
   1386     def _extra_kwargs(self, **kwargs):

/Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/schema.pyc in _extra_kwargs(self, **kwargs)
   1385 
   1386     def _extra_kwargs(self, **kwargs):
-> 1387         self._validate_dialect_kwargs(kwargs)
   1388 
   1389     #    @property

/Users/ihf/anaconda2/lib/python2.7/site-packages/sqlalchemy/sql/base.pyc in _validate_dialect_kwargs(self, kwargs)
    287                 raise TypeError(
    288                     "Additional arguments should be "
--> 289                     "named <dialectname>_<argument>, got '%s'" % k
    290                 )
    291             dialect_name, arg_name = m.group(1, 2)

TypeError: Additional arguments should be named <dialectname>_<argument>, got 'ondelete'

[Peter Schutt]

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', ondelete=u'CASCADE'), nullable=False, index=True)

[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 now and went back to just running your converted query. I now get:

NameError                                 Traceback (most recent call last)
<ipython-input-11-60d8f90ad86b> in <module>
      1 s = Session()
      2 subquery = (
----> 3     s.query(Entity_Tag.display_name)
      4     .filter(
      5         Contribution.receive_date > datetime.date(2005, 7, 1),

NameError: name 'Entity_Tag' is not defined

[Ira Fuchs]

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.

[Ira Fuchs]

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 a, civicrm_country f WHERE c.id = d.entity_id and tag_id = 6 and c.id = e.contact_id AND state_province_id = a.id  and e.country_id = f.id and  display_name not in ( SELECT display_name FROM civicrm_contribution, civicrm_contact c, civicrm_entity_tag d WHERE receive_date > \'2005-07-01\' and contact_id = c.id and c.id = entity_id and tag_id = 6 )')

This was working fine. (It is just my way of making sure I am connected to db and able to retrieve data (the old way).

On Thursday, August 22, 2019 at 6:56:56 PM UTC-4, Ira Fuchs wrote:

Weird. When I go back to just running the SQL query, I now get:

[Ira Fuchs]

In case it helps,the full output from this command (which was working yesterday) looks like this:

InternalError                             Traceback (most recent call last)
~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2264         try:
-> 2265             return fn()
   2266         except dialect.dbapi.Error as e:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in unique_connection(self)
    302         """
--> 303         return _ConnectionFairy._checkout(self)
    304 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
    759         if not fairy:
--> 760             fairy = _ConnectionRecord.checkout(pool)
    761 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
    491     def checkout(cls, pool):
--> 492         rec = pool._do_get()
    493         try:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    138                 with util.safe_reraise():
--> 139                     self._dec_overflow()
    140         else:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     67             if not self.warn_only:
---> 68                 compat.reraise(exc_type, exc_value, exc_tb)
     69         else:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    152             raise value.with_traceback(tb)
--> 153         raise value
    154 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    135             try:
--> 136                 return self._create_connection()
    137             except:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in _create_connection(self)
    307 
--> 308         return _ConnectionRecord(self)
    309 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
    436         if connect:
--> 437             self.__connect(first_connect_check=True)
    438         self.finalize_callback = deque()

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in __connect(self, first_connect_check)
    648                     pool.dispatch
--> 649                 ).exec_once(self.connection, self)
    650             if pool.dispatch.connect:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/event/attr.py in exec_once(self, *args, **kw)
    286                     try:
--> 287                         self(*args, **kw)
    288                     finally:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    296         for fn in self.listeners:
--> 297             fn(*args, **kw)
    298 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in on_connect(dbapi_connection, connection_record)
    188                         return
--> 189                     do_on_connect(conn)
    190 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/mysqldb.py in on_connect(conn)
    130                 cursor = conn.cursor()
--> 131                 cursor.execute("SET NAMES %s" % charset_name)
    132                 cursor.close()

~/anaconda2/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query

~/anaconda2/lib/python3.6/site-packages/pymysql/cursors.py in _query(self, q)
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in read(self)
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet

~/anaconda2/lib/python3.6/site-packages/pymysql/protocol.py in check_error(self)
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 

~/anaconda2/lib/python3.6/site-packages/pymysql/err.py in raise_mysql_exception(data)
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)

InternalError: (1115, "Unknown character set: 'utf8mb4'")

The above exception was the direct cause of the following exception:

InternalError                             Traceback (most recent call last)
<ipython-input-7-3ed2575ebc02> in <module>
----> 1 with engine.connect() as con:
      2     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 a, civicrm_country f WHERE c.id = d.entity_id and tag_id = 6 and c.id = e.contact_id AND state_province_id = a.id  and e.country_id = f.id and  display_name not in ( SELECT display_name FROM civicrm_contribution, civicrm_contact c, civicrm_entity_tag d WHERE receive_date > \'2005-07-01\' and contact_id = c.id and c.id = entity_id and tag_id = 6 )')

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2194         """
   2195 
-> 2196         return self._connection_cls(self, **kwargs)
   2197 
   2198     @util.deprecated(

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
    101                 connection
    102                 if connection is not None
--> 103                 else engine.raw_connection()
    104             )
    105             self.__transaction = None

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   2294         """
   2295         return self._wrap_pool_connect(
-> 2296             self.pool.unique_connection, _connection
   2297         )
   2298 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2267             if connection is None:
   2268                 Connection._handle_dbapi_exception_noconnection(
-> 2269                     e, dialect, self
   2270                 )
   2271             else:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
   1534             util.raise_from_cause(newraise, exc_info)
   1535         elif should_wrap:
-> 1536             util.raise_from_cause(sqlalchemy_exception, exc_info)
   1537         else:
   1538             util.reraise(*exc_info)

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    396     exc_type, exc_value, exc_tb = exc_info
    397     cause = exc_value if exc_value is not exception else None
--> 398     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    399 
    400 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    150             value.__cause__ = cause
    151         if value.__traceback__ is not tb:
--> 152             raise value.with_traceback(tb)
    153         raise value
    154 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2263         dialect = self.dialect
   2264         try:
-> 2265             return fn()
   2266         except dialect.dbapi.Error as e:
   2267             if connection is None:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in unique_connection(self)
    301 
    302         """
--> 303         return _ConnectionFairy._checkout(self)
    304 
    305     def _create_connection(self):

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in _checkout(cls, pool, threadconns, fairy)
    758     def _checkout(cls, pool, threadconns=None, fairy=None):
    759         if not fairy:
--> 760             fairy = _ConnectionRecord.checkout(pool)
    761 
    762             fairy._pool = pool

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in checkout(cls, pool)
    490     @classmethod
    491     def checkout(cls, pool):
--> 492         rec = pool._do_get()
    493         try:
    494             dbapi_connection = rec.get_connection()

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    137             except:
    138                 with util.safe_reraise():
--> 139                     self._dec_overflow()
    140         else:
    141             return self._do_get()

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     66             self._exc_info = None  # remove potential circular references
     67             if not self.warn_only:
---> 68                 compat.reraise(exc_type, exc_value, exc_tb)
     69         else:
     70             if not compat.py3k and self._exc_info and self._exc_info[1]:

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    151         if value.__traceback__ is not tb:
    152             raise value.with_traceback(tb)
--> 153         raise value
    154 
    155     def u(s):

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/impl.py in _do_get(self)
    134         if self._inc_overflow():
    135             try:
--> 136                 return self._create_connection()
    137             except:
    138                 with util.safe_reraise():

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in _create_connection(self)
    306         """Called by subclasses to create a new ConnectionRecord."""
    307 
--> 308         return _ConnectionRecord(self)
    309 
    310     def _invalidate(self, connection, exception=None, _checkin=True):

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in __init__(self, pool, connect)
    435         self.__pool = pool
    436         if connect:
--> 437             self.__connect(first_connect_check=True)
    438         self.finalize_callback = deque()
    439 

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/pool/base.py in __connect(self, first_connect_check)
    647                 pool.dispatch.first_connect.for_modify(
    648                     pool.dispatch
--> 649                 ).exec_once(self.connection, self)
    650             if pool.dispatch.connect:
    651                 pool.dispatch.connect(self.connection, self)

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/event/attr.py in exec_once(self, *args, **kw)
    285                 if not self._exec_once:
    286                     try:
--> 287                         self(*args, **kw)
    288                     finally:
    289                         self._exec_once = True

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/event/attr.py in __call__(self, *args, **kw)
    295             fn(*args, **kw)
    296         for fn in self.listeners:
--> 297             fn(*args, **kw)
    298 
    299     def __len__(self):

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in on_connect(dbapi_connection, connection_record)
    187                     if conn is None:
    188                         return
--> 189                     do_on_connect(conn)
    190 
    191                 event.listen(pool, "first_connect", on_connect)

~/anaconda2/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/mysqldb.py in on_connect(conn)
    129             if charset_name is not None:
    130                 cursor = conn.cursor()
--> 131                 cursor.execute("SET NAMES %s" % charset_name)
    132                 cursor.close()
    133 

~/anaconda2/lib/python3.6/site-packages/pymysql/cursors.py in execute(self, query, args)
    168         query = self.mogrify(query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query
    172         return result

~/anaconda2/lib/python3.6/site-packages/pymysql/cursors.py in _query(self, q)
    326         self._last_executed = q
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()
    330         return self.rowcount

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
    515                 sql = sql.encode(self.encoding, 'surrogateescape')
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows
    519 

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
    730         else:
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result
    734         if result.server_status is not None:

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in read(self)
   1073     def read(self):
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 
   1077             if first_packet.is_ok_packet():

~/anaconda2/lib/python3.6/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
    682 
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet
    686 

~/anaconda2/lib/python3.6/site-packages/pymysql/protocol.py in check_error(self)
    218             errno = self.read_uint16()
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 
    222     def dump(self):

~/anaconda2/lib/python3.6/site-packages/pymysql/err.py in raise_mysql_exception(data)
    107         errval = data[3:].decode('utf-8', 'replace')
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)

InternalError: (pymysql.err.InternalError) (1115, "Unknown character set: 'utf8mb4'")
(Background on this error at: http://sqlalche.me/e/2j85)

[Ira Fuchs]

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.

[Peter Schutt]

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.

[Ira Fuchs]

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.

--
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 sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/df9aa766-90d3-440b-8b48-18bfc47f568f%40googlegroups.com.

[Ira Fuchs]

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'))

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}]

To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Peter Schutt]

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.

To unsubscribe from this group and all its topics, send an email to sqlal...@googlegroups.com.

[Ira Fuchs]

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.

[Peter Schutt]

Good to hear and you're welcome:)