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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e6cca0bd-5adc-41e1-80bb-e2bdbc7f4541%40googlegroups.com.
