I tried to extend the model a bit further but I guess Datetime is specified
another way?
class Contribution(Base):
__tablename__ = "civicrm_contribution"
id = Column(Integer, primary_key=True)
contact_id = Column(Integer, nullable=False)
receive_date = Column(Datetime,nullable=False)
total_amount = Column(Decimal(20), 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/b7de0338-8301-4f12-b607-38ce29bbff63%40googlegroups.com.
