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)
On Friday, 23 August 2019 05:16:32 UTC+10, Ira Fuchs wrote:
>
> 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'
>
>
> On Wednesday, August 21, 2019 at 8:55:24 PM UTC-4, Peter Schutt wrote:
>>
>> 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.
>>
>> On Thursday, 22 August 2019 10:43:17 UTC+10, Peter Schutt wrote:
>>>
>>> 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
>>> <https://docs.sqlalchemy.org/en/13/faq/performance.html#i-m-inserting-400-000-rows-with-the-orm-and-it-s-really-slow>).
>>>
>>> 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
>>> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#building-a-relationship>
>>> 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}`.
>>>
>>> On Wednesday, 21 August 2019 10:20:30 UTC+10, Ira Fuchs wrote:
>>>>
>>>> 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, 2019 at 6:58:03 PM UTC-4, Ira Fuchs wrote:
>>>>>
>>>>> 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 at 6:15:46 PM UTC-4, Ira Fuchs wrote:
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>> On Tuesday, August 20, 2019 at 5:15:36 PM UTC-4, Ira Fuchs wrote:
>>>>>>>
>>>>>>> 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)
>>>>>>>
>>>>>>> On Tuesday, August 20, 2019 at 3:59:55 PM UTC-4, Ira Fuchs wrote:
>>>>>>>>
>>>>>>>> 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.
>>>>>>>>
>>>>>>>> On Monday, August 19, 2019 at 7:11:59 AM UTC-4, Peter Schutt wrote:
>>>>>>>>>
>>>>>>>>> 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
>>>>>>>>> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#declare-a-mapping>.
>>>>>>>>>
>>>>>>>>> Also, you can read the Column API
>>>>>>>>> <https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Column>
>>>>>>>>> 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
>>>>>>>>> <https://docs.sqlalchemy.org/en/13/core/constraints.html#sqlalchemy.schema.ForeignKey>
>>>>>>>>> and
>>>>>>>>> touched on in the orm tutorial here
>>>>>>>>> <https://docs.sqlalchemy.org/en/13/orm/tutorial.html#building-a-relationship>
>>>>>>>>> .
>>>>>>>>>
>>>>>>>>> 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.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Monday, 19 August 2019 12:36:21 UTC+10, 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 have not mapped any
>>>>>>>>>> tables
>>>>>>>>>> to classes.
>>>>>>>>>>
>>>>>>>>>> On Sunday, August 18, 2019 at 8:54:57 PM UTC-4, Peter Schutt
>>>>>>>>>> wrote:
>>>>>>>>>>>
>>>>>>>>>>> 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?
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Monday, 19 August 2019 02:34:05 UTC+10, Ira Fuchs wrote:
>>>>>>>>>>>>
>>>>>>>>>>>> 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.
>>>>>>>>>>>>
>>>>>>>>>>>>
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d4f70281-033f-4f52-abf5-512ad6ed9159%40googlegroups.com.
