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/e32e470c-5e06-45fd-a6c3-e768b587c31e%40googlegroups.com.
