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/e442aa66-55ee-4f4f-949d-df3d8145c4c9%40googlegroups.com.
