Re: [sqlalchemy] Re: Connection error

[Rich Shepard]

On Wed, 16 Jun 2021, Ryan Bandler wrote:


Yeah I connect to the DB over localhost:5432 via psql all the time and
also haven't had any issues connecting via psycopg2. I also have no issue
connecting to the database in sqlalchem, it's only an issue with
sqlacodegen.


Ryan,

Just to confirm, you're running sqlacodegen from the OS shell and not from
the python REPL, correct?

Rich

--
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/alpine.LNX.2.20.2106160808450.6174%40salmo.appl-ecosys.com.

Re: [sqlalchemy] Connection error

[Rich Shepard]

On Tue, 15 Jun 2021, Ryan Bandler wrote:


I am a first-time SQLalchemy user planning on using SQLalchemy on a new
project.


Ryan,

I started to learn SA long ago, but put off the project. Now I'm starting
over again with much to learn.


We already have an established postgres database (currently still on
localhost) which I do not want to handwrite the SQLalchemy model for. So I
am planning to use sqlacodegen. Unfortunately, sqlacodegen is giving me:


My postgres databases exist and have data. I used the short version of
declarative models; don't know if the concept and syntax still holds for SA
version 1.4.x

For one project the model.py file is:
"""
  This is the SQLAlchemy declarative mapping python classes to postgres
  tables for the business tracker.
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import CheckConstraint
from sqlalchemy.orm import Session
from sqlalchemy.dialects import postgresql

"""Base = declarative_base()"""
Base = automap_base()

engine = create_engine('postgresql+psycopg2:///bustrac')

# reflect the tables
Base.prepare(engine, reflect=True)

State = postgresql.Enum('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 
'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 
'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 
'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 
'WV', 'WI', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 'PE', 
'QC', 'SK', 'YT', name='states')

Industries = Base.classes.industries
Status = Base.classes.status
StatusTypes = Base.classes.statusTypes
ActivityTypes = Base.classes.activityTypes
Organizations = Base.classes.organizations
Locations = Base.classes.locations
People = Base.classes.people
Activities = Base.classes.activities
Projects = Base.classes.projects

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

HTH,

Rich

--
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/alpine.LNX.2.20.2106151009500.3763%40salmo.appl-ecosys.com.

[sqlalchemy] checking in

[Rich Shepard]

I've not worked with SQLAlchemy for several years but now want to use it in
a couple of applications. I've not seen messages on this maillist for a very
long time so I tried subscribing and learned that I'm still subscribed.

Am I the only one on this list now?

If not I wonder why messages aren't arriving in my INBOX.

Rich

--
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/alpine.LNX.2.20.2106141423470.11603%40salmo.appl-ecosys.com.

[sqlalchemy] Not receiving messages posted to the group

[Rich Shepard]

I'm subscribed to the group but do not receive email copies of posted
messages. I've looked on the forum's web page and did not see where to turn
on email participation.

Please point me to where I can control receiving/posting messages via email.

TIA,

Rich

--
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/alpine.LNX.2.20.2101131532560.32588%40salmo.appl-ecosys.com.

[sqlalchemy] Defining model.py for existing database

[Rich Shepard]

I'm working on learning SQLAlchemy to use for my business tracking
application. I have the views written (in tkinter) and two versions of
model.py as the model. Long ago I asked about using base or declarative base
but no longer have that thread saved.

I've read about base mapping and declarative base and am still confused.

The short version, which I believe is correct, references the existing,
populated postgresql-12 tables:

"""
  This is the SQLAlchemy declarative mapping python classes to postgres
  tables for the business tracker.
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import CheckConstraint
from sqlalchemy.orm import Session
from sqlalchemy.dialects import postgresql

"""Base = declarative_base()"""
Base = automap_base()

engine = create_engine('postgresql+psycopg2:///bustrac')

# reflect the tables
Base.prepare(engine, reflect=True)

state_code = postgresql.Enum('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 
'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 
'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 
'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 
'WA', 'WV', 'WI', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 
'PE', 'QC', 'SK', 'YT', name='state_code')

Industries = Base.classes.industries
Status = Base.classes.status
StatusTypes = Base.classes.statusTypes
ActivityTypes = Base.classes.activityTypes
Organizations = Base.classes.organizations
Locations = Base.classes.locations
People = Base.classes.people
Activities = Base.classes.activities
Cases = Base.classes.cases
Projects = Base.classes.projects

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

The longer version defines the classes for each table.

When I fully understand the model format I'll ask for where to start in the
docs to learn how to structure the controller portion using .sql scripts
already written.

TIA,

Rich

--
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/alpine.LNX.2.20.2012181231590.29974%40salmo.appl-ecosys.com.

Re: [sqlalchemy] Re: AM/PM question [ANSWERED]

[Rich Shepard]

On Fri, 20 Mar 2020, Jonathan Vanasco wrote:


It doesn't matter when the time is entered or what it is supposed to
reflect. The best option -- by a wide margin -- for storing any time
values is in a TIMESTAMP column ...


Jonathan,

Thanks. I've not before needed this information and I appreciate your
providing the insight I need.

Best regards,

Rich

--
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/alpine.LNX.2.20.2003201211150.2460%40salmo.appl-ecosys.com.

Re: [sqlalchemy] Re: AM/PM question

[Rich Shepard]

On Fri, 20 Mar 2020, Jonathan Vanasco wrote:


The common approach to this situation is storing the data as a 24hour
timestamp in the database/sqlalchemy and converting it to a 12hour am/pm
for display/editing.

You could use a "12 hour" option and an enum column for am/pm or a string.
You're going to have a much easier time in the longterm by using a standard
timestamp though.


Jonathan,

I understand this working if the time to be entered is the current time, but
not if the time is different. For example, a sample collection time and its
analysis times will be different and, in my application, entered well after
they occured. That's why I'm asking for guidance.

For example, a sample could be taken on 1 June at 11:00 hours, transported
to the analytical laboratory where it was analyzed on 2 June at 13:50 hours.
The report is sent to the user on 10 June and those times are entered in the
application any time after that.

Regards,

Rich

--
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/alpine.LNX.2.20.2003201101410.2460%40salmo.appl-ecosys.com.

[sqlalchemy] AM/PM question

[Rich Shepard]

A couple of sqlalchemy classes need to store user-entered times. The
intended user audience is probably unfamiliar with using a 24-hour clock so
I'll use the ttk.Radiobutton to have the user select AM or PM for the period.

How do I represent this variable in the class definition of columns? The
widget returns a boolean True/False for both choices, AM and PM so do I use
a String containing 'AM' or 'PM'? Are there examples for this available?

Hope you're all staying healthy,

Rich

--
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/alpine.LNX.2.20.2003200956060.2460%40salmo.appl-ecosys.com.

Re: [sqlalchemy] Re: Composite key; one field is also foreign key [RESOLVED]

[Rich Shepard]

On Tue, 18 Jun 2019, Jonathan Vanasco wrote:


adding `primary_key=True` to each of the `Column` constructors should do it.


Thanks, Jonathan.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.1906181504410.21630%40salmo.appl-ecosys.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Composite key; one field is also foreign key

[Rich Shepard]

I understand that multiple fields in a declarative base class can be
specified as a composite primary key. In my application rather than using a
unique, serialized primary key for each table/field I use a foreign key
reference to a row in the locations table and a date. For example,

site_name = Column(Integer, ForeignKey('locations.site_name', \
onupdate='CASCADE', ondelete='RESTRICT'))
sampdate = Column(Date, nullable=False)

How do I specify both as a composite primary key?

TIA,

Rich


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.1906181308050.21630%40salmo.appl-ecosys.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Importing module from another subdirectory [UPDATE 2]

[Rich Shepard]

On Wed, 17 Apr 2019, Rich Shepard wrote:


Getting closer. Added a blank __init.py__ in the models/ subdirectory. Now
I'm looking for the proper syntax to import the relevant class within that
module.



So more work on my part.


I'm moving this thread to the python mail list; it's a python issue not an
SA issue.

Trying to run the module using Python3-3.7.3's pdb I get the same error.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Importing module from another subdirectory [UPDATE]

[Rich Shepard]

On Wed, 17 Apr 2019, Rich Shepard wrote:


What is the proper syntax to import models as m a in a view module, e.g.,
activities.py? The syntax, 'import model as m' fails because it is not in
the same subdirectory as the importing module.


Getting closer. Added a blank __init.py__ in the models/ subdirectory. Now
I'm looking for the proper syntax to import the relevant class within that
module.

import model.activities as a

is not working:
Traceback (most recent call last):
  File "activities_data_entry.py", line 1, in 
import model.activities as a
ModuleNotFoundError: No module named 'model'

So more work on my part.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Tkinter search dialog box for model row

[Rich Shepard]

My web search for an existing Tkinter search dialog box found nothing. I'm
looking for a pointer to a module or method if one exists.

In this application the user needs to find database rows based on two class
columns: lname and fname and display results in the appropriate data entry
dialog view.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Importing module from another subdirectory

[Rich Shepard]

My application intends to follow the MVC pattern. The model.py module is 
../model/model.py while the Tkinter views are in ../views/. Both are

relative to the application's main directory:

bustrac/
  README.rst
  bustrac.py*
  controller/
  images/
  model/
  scripts/
  views/

What is the proper syntax to import models as m a in a view module, e.g.,
activities.py? The syntax, 'import model as m' fails because it is not in
the same subdirectory as the importing module.

My previous use of python has had all files in the same directory so I've
not before had to learn how to address this issue. Pointers appreciated.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Documentation options

[Rich Shepard]

On Sat, 30 Mar 2019, Cameron Simpson wrote:


If it is of use I have this shell function:

  open_dash () {
  open "dash://$*"
  }

and this alias:

alias //=open_dash

so that I can go "// search terms here" from the shell command line.  Avoids 
some painful touchpad/mouse mucking around.


Cameron,

Zeal is a command line tool; a bash shell script is probably not necessary.
Most of my work is in virtual terminals and I use the trackball to switch
among them. There are some GUI applications that are highly productive and
they all take keyboard commands as well as those from the pointing thingie.

Thanks for the suggestion,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Rich Shepard wrote:


This looks like the ideal solution. I'll install Dash and use it.


Actually, I won't because it's an Apple product for their macOS and iOS.
However, slackbuilds.org has a package called Zeal (a simple offline
documentation browser inspired by Dash) that works with linux and that Other
OS.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Mike Bayer wrote:


I'd prefer any code samples are shared on-list here for the benefit of all
to see, thanks.


Mike,

I was unsure about the proper protocol. I'll post the module here Real Soon
Now.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Documentation options

[Rich Shepard]

On Fri, 29 Mar 2019, Xavier Bustamante Talavera wrote:


I use Dash  to read SQLAlchemy documentation,
which is great to search through it and works offline (it downloads the
whole docs).


Xavier,

This looks like the ideal solution. I'll install Dash and use it.

Many thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Documentation options

[Rich Shepard]

On Thu, 21 Mar 2019, Mike Bayer wrote:


sounds like automap:
https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html


Mike,

May I send you models.py (off the mail list) to check that I have correctly
applied automap?

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Documentation options

[Rich Shepard]

On Thu, 21 Mar 2019, Mike Bayer wrote:


sounds like automap:
https://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html


Mike,

While this does not look familiar, I'll carefully read the page again until
I really understand it.

Now, model.py contains classes for each table in the postgres database, and
I thought there was a way to associate the classes with the tables yet
automap doesn't tickle my memory. So, more study is on the schedule.

Thanks again,

Rich


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Documentation options

[Rich Shepard]

On Thu, 21 Mar 2019, Mike Bayer wrote:


oh sure, I meant it uses up an enormous amount of memory / CPU to build
the PDF. it looks terrible too because sphinx's templates aren't very
good, plus SQLAlchemy's docs have a bunch of custom things going on that
format even more badly.


Mike,

Okay. You convinced me to stick with the on-line versions.

I have the database tables created and populated, and the tkinter GUI views
(at least, most of them) written, and now I need to learn how to apply SA
between the two.

Long ago I saw the docs that explained how to work with an existing database
rather than using the classes to create the tables. I'm trying to find that
again in my e-mail archives.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Documentation options

[Rich Shepard]

On Thu, 21 Mar 2019, Mike Bayer wrote:


not currently, however you can do a sphinx pdf build yourself if you
feel like installing LaTeX,


Mike,

I write > 90% of my documents using LaTeX (with the LyX GUI front end).
Sphinx is a new one for me so I'll go look at it.


unfortunately SQLAlchemy's PDF is enormous, has a lot of formatting
problems, and the readthedocs site wasn't able to build it, the
current tools are not reliable enough for me to publish this myself.


Postgres docs are also rather large and I find it easier to search for
specific information in a PDF.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Documentation options

[Rich Shepard]

Are there PDF versions of the docs available for downloading and reading? I
don't find an answer on the web site.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Making Python3 list from set returned by SQL query

[Rich Shepard]

On Mon, 11 Mar 2019, Simon King wrote:


Using your Industries class, a function to return that list of names
could look like this:

def get_industry_names(session):
   q = session.query(Industries)
   return [i.ind_name for i in q.all()]

Does that do what you want?


Simon,

It probably will, but I don't yet have sufficient code to test it. I'm now
writing the views for all classes (which is what prompted my question) and
will test this function as soon as practical.

Thanks very much,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Making Python3 list from set returned by SQL query

[Rich Shepard]

I'm not sure exactly what you mean here. You can write a function that
iterates over the rows and selects the attributes that you want.


Simon,

Let me try to be more clear.

There's a database table named 'organizations', and an SA model class
associated with it. One of the columns in the organization class, industry,
is defined as:

industry = Column(String, default='Other',
  ForeignKey('industries.ind_name', onupdate="CASCADE", 
ondelete="RESTRICT"))

The industry classe is defined prior to the organization class:

class Industries(Base):
__tablename__ = 'industries'

ind_name = Column(String, primary_key=True)

When I query the database table using psql,
select * from industries order by ind_name;
psql displays this:

ind_name 
-

 Attorney
 Business
 Consultant
 Energy
 Farming
 Forest products
 Government
 Livestock
 Manufacturing
 Maritime
 Mining
 Other
(12 rows)

My assumption is that SA will return the same results. I want to present
these names in the tkinter form for the organizations class within a
ttk.Combobox by dropping the first two, and last, rows and making a list of
the ind_name strings. There is another column in this class (and one in
another class) where acceptable values for those variables are also in
lookup tables.

What is the most efficient way to do this? I thought that a function which
ignores the first two rows then addes all but the last to a list would do
the job so confirmation is helpful.

If I'm still not explaining well enough I'll try again. :-)

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Making Python3 list from set returned by SQL query

[Rich Shepard]

Two classes in the model have columns with values constrained to specific
text strings; one table has two such columns, another table has one.

Because new strings might be added to the lists of allowed values for that
column each has the acceptable values as rows in a table rather than in a
column constraint.

SQL queries processed by SA to select rows in each table return sets. How do
I transpose each set to a python list can will be used by tkinter to display
them in a ttk.Combobox?

Because these lists need to be present before the classes for the associated
tables are processed how should the queries and lists be represented in the
view module? In a separate class?

If the answers are in the SA docs please point me to them.

And, if my questions are not sufficiently clear let me know and I'll try to
be more explicit.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] User name and password on local database

[Rich Shepard]

On Tue, 5 Mar 2019, Mike Bayer wrote:


sure, if your PG database allows you to connect using "trust" or other
localhost-style connection you can omit everything from the URL


Thanks, Mike.

Yes, I have all hosts on the LAN set to trust as nothing is exposed to the
world.

Best regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] User name and password on local database

[Rich Shepard]

I'm writing an application for my business use; I'll be the only user and
postgres is on the same server/workstation host. When I specify the engine
using create_engine() can I leave off at least my password if not both it
and my username? The database to which SA is connecting is owned by me.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Organizing a project

[Rich Shepard]

On Mon, 4 Mar 2019, Jonathan Vanasco wrote:


We "define" the model in a separate package - all the classes and
relationships are in there. There are database support items as well, and
some of the advanced/business logic that manipulate the ORM objects. By
advanced-database-specific logic, an example might be: resetting a
password is a function that checks the existing password against current &
past ones, updates the current credentials table, and migrates a consumed
credential into a table of invalid previously used options - it's a
multi-step database update.


Jonathan,

Thanks for clarifying.

For this and the next application I need to build the model (in model.py) is
the SQLAlchemy translation of the postgres tables; with this application
there are 9 tables/classes. No other application will use these.


The webapps typically have a "model" namespace, but that just invokes the
'shared' model package and contains any application/implementation
specific model logic. Everything is written so it can be easily migrated
into the shared model package so all of the coordinated apps/utilities can
leverage it.


I have a module called commonDlgs.py which contains re-useable classes, such
as password login, quitter (asks 'Are you sure?'), and generic message box.


This is just a personal preference my I have gravitated towards with my
team over the past few years.


You're really helped me resolve my questions. Now I know that I can put
classes in whatever modules and subdirectories I want as long as import them
where they are used, and it really doesn't matter what the structure is so
long as it is easy for others to follow. The current application is for my
business needs; the next one will be given away and hosted on github.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Organizing a project

[Rich Shepard]

On Sun, 3 Mar 2019, Jonathan Vanasco wrote:


I generally write the SqlAlchemy models as a separate package, then import
that into my MVC/etc patterned app.



In terms of the SqlAlchemy logic, some of that I keep in the models
package, others are in the core app.

...

So my general advice is to approach things with two packages, one for [M]
and the other for [VC]


Jonathan,

I see three different approaches in the above: 1) a separate SA package; 2)
some SA in the model, some in the application class; and 3) a model module
and a combined view/controller module. Please help me distinguish among
them.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Organizing a project

[Rich Shepard]

Starting my first SA project (have one more planned after this one's
working) and appreciate advice on organizing project files. I understand the
MVC pattern and am trying to apply it with subdirectories of model/, view/,
and controller/ yet I'm unsure where to place SQLAlchemy.

This is the overall structure: the backend is a postgresql database, SA will
access it via psycopg2 (the engine should be properly configured for this),
and the GUI is being written using tkinter.

Now, ../model/model.py has the classes that represent the postgres table;
../views/ contains four files: data_entry_dialogs.py, ttkcalendar.py, 
commonDlgs.py, and data_entry_validators.py; and ../controller/ is empty.


One of the classes in data_entry_dialogs.py has two ttk.Combobox widgets
whose values are in two classes in models.py. I assume that a properly
structured SA statement (not yet written or tested) can select the rows from
the two tables, store them in a list, and that list passed to the
ttk.Comboboxes for their input_arg values. I'll work on an appropriate
statement RSN, but now want to learn whether to put all SA code in
models.py, in the model/ directory, or the controller/ directory.

If this is in the ORM tutorial I've missed seeing it so pointers to docs
that address these two issues are very welcome.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Expressing a SQL DOMAIN as a class

[Rich Shepard]

On Mon, 7 Jan 2019, Mike Bayer wrote:


PG's ENUM does work that way, below the same type is shared:

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.dialects import postgresql

Base = declarative_base()

State = postgresql.ENUM("AK", "AL", "CO", "NY", name="states")

class A(Base):
   __tablename__ = 'a'

   id = Column(Integer, primary_key=True)
   data = Column(State)


class B(Base):
   __tablename__ = 'b'
   id = Column(Integer, primary_key=True)
   data = Column(State)


  Thanks, Mike.

Best regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Expressing a SQL DOMAIN as a class

[Rich Shepard]

On Mon, 7 Jan 201U9, Mike Bayer wrote:


Postgresql has ENUM within CREATE TYPE:
https://www.postgresql.org/docs/9.1/datatype-enum.html


Mike,

  Thank you. I hadn't looked at the postgres docs for it.


I'm not sure if this is a "DOMAIN" behind the scenes or what.


  Postgres supports the SQL DOMAIN. Whether enum is equivalent is yet to be
determined. :-)


This SQL syntax is directly supported with the PG ENUM type:
https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html?highlight=enum#enum-types
which is also available from the generic enum type:
https://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Enum,
as long as native_enum=True on that object.

Enum is pretty popular so it should work well.


  Thanks for the URLs. I glanced at the SA constraint doc's section on enum
but have not yet carefully read it.

  I'm using the domain because the same column check constraint applies to
two tables in this application so it makes sense to write it once and have
it applied everywhere there's a state_code column.

Best regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Expressing a SQL DOMAIN as a class

[Rich Shepard]

On Mon, 7 Jan 2019, Mike Bayer wrote:


there's no built-in construct for CREATE DOMAIN but we do support
reflection of custom domains, to do CREATE DOMAIN you can just use a
DDL() construct:
https://docs.sqlalchemy.org/en/latest/core/ddl.html?highlight=ddl,
the DOMAIN you have there looks like of like an ENUM though, we have
built in support for that would that be better?


Mike,

  It is an ENUM; a long list of two-charater state and province codes. If
that would be the better choice than a DDL() I'll certainly learn how to
implement it that way.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Expressing a SQL DOMAIN as a class

[Rich Shepard]

On Mon, 7 Jan 2019, Rich Shepard wrote:


and in postgres tables is this attribute:

 state_code char(2),

which is in the equivalent model classes; e.g.,

 org_state = Column(String)


  Oops! This should also be state_code.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Expressing a SQL DOMAIN as a class

[Rich Shepard]

An application I'm developing (postgres back end) has a SQL DOMAIN as a
check constraint on state/province codes. This is different from the SA
ORM's domain perspective. The SQL statement is:

CREATE DOMAIN the_state_code AS char(2)
DEFAULT '??'
CONSTRAINT valid_state_code
CHECK (value IN ('AL', 'AK', 'AZ', ...));

and in postgres tables is this attribute:

  state_code char(2),

which is in the equivalent model classes; e.g.,

  org_state = Column(String)

My question is how I add the CREATE DOMAIN DDL in models.py. I am not seeing
the answer in the SA docs.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Table CheckConstraint() limit?

[Rich Shepard]

On Mon, 7 May 2018, Mike Bayer wrote:


the CHECK constraint only accepts a single SQL expression.   Above, it
looks like you re looking to add a CHECK constraint for each
expression:

__table_args__ = (
  CheckConstraint(wtemp_unit.in_(['C', 'F'])),
  CheckConstraint(atemp_unit.in_(['C', 'F'])),
 # etc.

)


Mike,

  Yes, that's what I need to do. I'll add each separately.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Table CheckConstraint() limit?

[Rich Shepard]

  The module, models.py, has five classes. Each has table constraints: three
have a single table constraint, one has two table constraints, and one has
seven table constraints. SA seems to reject more than four table
constraints.

  With the last three commented out, there's no error:

__table_args__ = (
CheckConstraint(
wtemp_unit.in_(['C', 'F']),
atemp_unit.in_(['C', 'F']),
vel_unit.in_(['ft/sec','m/sec']),
disc_unit.in_(['cfs','cms']),
#depth_unit.in_(['in', 'ft', 'cm', 'm']),
#width_unit.in_(['in', 'ft', 'cm', 'm']),
#slope_unit.in_(['deg', '%'])
),
)

But, when I allow the fifth there's a problem:

$ ./openEDMS.py 
Traceback (most recent call last):

  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 682, 
in __getattr__
return getattr(self.comparator, key)
AttributeError: 'Comparator' object has no attribute 'constraints'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./openEDMS.py", line 18, in 
import models
  File "/home/rshepard/development/openEDMS/models.py", line 144, in 
class Physical(Base):
  File "/home/rshepard/development/openEDMS/models.py", line 178, in Physical
depth_unit.in_(['in', 'ft', 'cm', 'm']),
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 2791, 
in __init__
self._set_parent_with_dispatch(table)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/base.py", line 431, in 
_set_parent_with_dispatch
self._set_parent(parent)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 2708, 
in _set_parent
Constraint._set_parent(self, table)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 2553, 
in _set_parent
parent.constraints.add(self)
  File "/usr/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 688, 
in __getattr__
key)
AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has 
an attribute 'constraints'

  I've looked in the docs and searched the web without finding mention of a
limit. Looking at the list of constraints I'm not seeing syntatic
differences in the latter three. Please show me what I've missed.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Data type Float not recognized [FIXED]

[Rich Shepard]

On Mon, 7 May 2018, Rich Shepard wrote:


 What have I missed?


  Forgot to import that data type to the module.

Mea culpa!

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Data type Float not recognized

[Rich Shepard]

  SQLAlchemy-1.2.7 installed here.

  Model column definition is:

quant = Column(Float, nullable=False)

yet Python3 tells me that Float is not defined:

quant = Column(Float, nullable=False) 
NameError: name 'Float' is not defined


  If Float was a variable I created it would need to be declared/initialized
prior to being used, but in this context it's a model data type similar to
Integer or String.

  Adding a precision parameter makes no difference.

  What have I missed?

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

On Sun, 6 May 2018, Mike Bayer wrote:


the second approach is probably more common, as it's more compact.


  Thanks, Mike. It works well here.

Best regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

On Sun, 6 May 2018, Mike Bayer wrote:


here is the correct way to construct and append the constraint:


  Thanks, Mike. I tried following the example from the docs and could not
find what I missed.

  You provide two approaches. Is there a preference for one over the other,
perhaps based on context?

  This application, and others I plan to write, have columns restricted to
specific content for consistency and data integrity.

Much appreciated,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

On Sun, 6 May 2018, Rich Shepard wrote:


 I'm missing how to properly use the above in my models.py module.


Mike,

  And I have read the brief description of the CHECK Contstraint in the
'Defining Constraints and Indexes' section of the docs.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

On Fri, 4 May 2018, Mike Bayer wrote:


you're looking for a table-level check constraint with IN:
table.append_constraint(
  CheckConstraint(table.c.data_type.in_('A', 'B', 'C'))
)


Mike,

  I'm missing how to properly use the above in my models.py module.

  For example:

class Sites(Base):
__tablename__ = 'locations'

site_id = Column(Integer, primary_key=True)
site_name = Column(String(16), nullable=False)
data_type = Column(String(12), nullable=False)
source = Column(String(64))
lat = Column(String(9))
lon = Column(String(9))
stream = Column(String(32))
basin = Column(String(32))
comment = Column(String)

locations.append.constraint(
CheckConstraint(locations.data_type_in('Biogical', 'Chemical', 
'Microbial', 'Physical', 'Multiple'))
)

  Python shows me this error:

Traceback (most recent call last):
  File "./openEDMS.py", line 18, in 
import models
  File "/home/rshepard/development/openEDMS/models.py", line 20, in 
class Sites(Base):
  File "/home/rshepard/development/openEDMS/models.py", line 37, in Sites
CheckConstraint(locations.data_type_in('Biogical', 'Chemical', 'Microbial', 
'Physical', 'Multiple'))
NameError: name 'locations' is not defined

  If I change the table-level constraint from the tablename (locations) to
the class name (Sites) python gives me the equivalent NameError. What syntax
error have I made here?

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

On Fri, 4 May 2018, Mike Bayer wrote:


you're looking for a table-level check constraint with IN:


Mike,

  Oh. I missed that since I write my postgres schema constraints on the
column.


alternatively, just use the backend-agnostic Enum type with native=False:
http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum
gives you the same CHECK constraint


  I'll look at that.

  Do you recommend one approach over the other for a new SQLAlchemy
developer?

Best regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Column CheckConstraint() question

[Rich Shepard]

  In postgres (and I believe also in sqlite3) values in a table column can
be restricted to certain values.

  In models.py the class Sites() includes this column:

data_type = Column(String(12), nullable=False,
 CheckConstraint('Biogical', 'Chemical', 'Microbial', 'Physical',
 'Multiple'))

but Python doesn't like this syntax:

Traceback (most recent call last):
  File "./openEDMS.py", line 18, in 
import models
  File "/home/rshepard/development/openEDMS/models.py", line 28
data_type = Column(String(64), nullable=False, CheckConstraint('Biogical',
  'Chemical', 'Microbial',
  'Physical', 'Multiple')) ^
SyntaxError: positional argument follows keyword argument

  My web search found examples and the SA CHECK constraint description, but
none used a list of strings as acceptable values. I need to learn how to
implement this constraint as there are several model classes that use it.

Rich


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Integrating SQLAlchemy and wxPython4

[Rich Shepard]

  I'm developing my first application using SQLAlchemy-1.2.7 and
wxPython-4.0.1. I've found a 6-year-old blog post by Mike Driscoll
illustrating a simple example. Are there other examples, tutorials, or open
source applications from which I could learn?

  So far I have the database tables as classes in models.py and wxPython4
modules for the UI. Now I need to learn how to integrate SA and wxPython
into a working application. All suggestions recommendations are welcome.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Two application versions, different back ends

[Rich Shepard]

On Mon, 26 Mar 2018, Jonathan Vanasco wrote:


Pay close attention to which database functions you use, and how you use
datetime fields. Those are two things were SQLite tends to differ from the
other common databases.

You can handle any of those differences using dialect specific custom
compilers in SqlAlchemy
(http://docs.sqlalchemy.org/en/latest/core/compiler.html)

There's no need to do anything now, but it's helpful to know what is likely
to break when switching the model's datastore to postgresql.


Jonathan,

  Thank you. That's good information for me to know.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Two application versions, different back ends

[Rich Shepard]

On Mon, 26 Mar 2018, Mike Bayer wrote:


this is a very open-ended question that can't be answered with a single
document. SQLAlchemy will allow for standard SQL and DDL to work across
both platforms transparently as well as differences in INSERT mechanics,
and additionally transactional semantics will work. But beyond that you
would need to be familiar with what database features you wish to use that
may require special attention.

General dialect documentation is at;

http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html


  Thanks, Mike.

  I'm working now on the single-user, SQLite3 version and will return to
this issue when that's functioning.

Much appreciated,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Two application versions, different back ends

[Rich Shepard]

  I'm starting to write an application which will have two versions. One is
single-user with sqlite3 as the backend, the other is multi-user with
postgres as the backend. The single-user version will be written first.

  Both will be placed on github as F/OSS applications.

  Please point me to the docs where I can learn what differences I need to
make to accommodate both versions.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] First application: visual linting requested

[Rich Shepard]

  Several years ago I started a project with SQLAlchemy but dropped
development to go in a different direction. Recently returning to the
project I recognized my error and have returned to SQLAlchemy for this
application (using version 1.2.1).

  I've read the ORM tutorial and followed links on foreign keys and
constraints. I _think_ that my syntax is correct but imports and other
details might be out of sequence so I would like someone with experience to
look at the file for me as a visual lint, checking for syntax or other
errors. I'll send the file off the mail list if you are willing to do this
for me.

  Getting this feedback will increase my confidence in correctly applying
SQLAlchemy as I continue building this application.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] E901 TokenError: EOF in multi-line statement [SOLVED]

[Rich Shepard]

On Sat, 25 Jul 2015, Rich Shepard wrote:


   curr_value = = Column(Unicode(32), nullable = False)
   changed_by = = Column(Unicode(32), nullable = False)


  These were the problem.

Rich

[sqlalchemy] E901 TokenError: EOF in multi-line statement

[Rich Shepard]

  When running flake8 on the schema file the error, 'E901 TokenError: EOF in
multi-line statement' is reported for the line following the last line of
code at the end of the file. Web searches show that error means a missing
quote (' or ), ')', ']', or '}'. There are no missing closures in the last
two classes:

class ChangedData(Base):
__tablename__ = 'changedData'

change_id = Column(Integer, Sequence('change_seq'), primary_key = True)
which_table = Column(Unicode(32), nullable = False, unique = True)
which_attrib = Column(Uicode(32), nullable = False, unique = True)
when_changed = Column(Timestamp, nullable = False, unique = True)
curr_value = = Column(Unicode(32), nullable = False)
new_value =  Column(Unicode(32), nullable = False)
changed_by = = Column(Unicode(32), nullable = False)
reason = Column(Text)
__table_args__ = (UniqueConstraint(which_table, which_attrib,
when_changed))


class ReportCopies(Base):
__tablename__ = 'reportCopies'

rpt_nbr = Column(Integer, Sequence('rpt_seq'), primary_key = True)
tbl_name = Column(Unicode(16), nullable = False)
rpt_date = Column(Date, nullable = False)
rpt_desc = Column(Text, nullable = False)
rpt_filename = Column(BLOB, nullable = False)

  What might be causing this error that I'm not seeing?

Rich

[sqlalchemy] Syntax Checking

[Rich Shepard]

  Because this is my first SQLAlchemy project and the schema file has 657
lines I would like to check for syntax errors before proceeding with the
next step in application development. I find no index in the SA manual and
cannot find the string 'syntax check' in the PDF file. Web search turns up
nothing (which might be due to wrong search phrase).

  Is there a way to check for proper syntax prior to having sufficient code
to try running the application?

Rich

Re: [sqlalchemy] Syntax Checking

[Rich Shepard]

On Fri, 24 Jul 2015, Mike Bayer wrote:


Python syntax or SQL syntax?


Mike,

  The former.


Typically in Python we rely on linters and runtime checks for this, same
idea with SQL.


  Have not used a lint before with Python, but will run it on my SQLAlchemy
code.

  I know that SQLite and postgres let me know in no uncertain terms when my
SQL is off.

  And thanks for yesterday's reply. It forced me out of my rut to look for
the proper way to relate all the information ... which I found.

Regards,

Rich

Re: [sqlalchemy] Syntax Checking

[Rich Shepard]

On Fri, 24 Jul 2015, Jonathan Vanasco wrote:


In terms of linters, `flake8` (https://pypi.python.org/pypi/flake8) catches
most mistakes I've made with SqlAlchemy.

It's also useful to start writing Unit Tests that will interact with your
SqlAlchemy models in predicted ways -- in addition to continually checking
core functionality.


Jonathan,

  Sound advice and I'll take both. Haven't used lint since I left C for
Python, and understand the value of unit testing while I know nothing about
the details of implementing them. Will learn (and apply) flake8 and learn
about unit testing before proceeding further.

Much appreciated,

Rich

Re: [sqlalchemy] Many-to-Many Table

[Rich Shepard]

On Thu, 23 Jul 2015, Mike Bayer wrote:


conceptually not a big deal but API-wise has many mistakes. Sequences
don't work with unicode, there is no value parameter, the table has no
primary key.


Mike,

  True, I don't want a sequence but the location ID, there needs to be a
value column, and others should be moved into the parameter table.

  I thought a many-to-many table did not need a primary key because it would
be accessed by (in this case) location or paramter.

Thanks,

Rich

[sqlalchemy] Many-to-Many Table

[Rich Shepard]

  Originally posted here on June 4th, but no one responded. I'm now back on
this project and this should be the last table I need to add to the schema.
I would appreciate your review of the below class and whether it is good to
go or needs your modifications.

  Section 2.1.15 in the 1.0.8 manual describes building an association table
for many-to-many relationships. My application has a table that associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I separate
site and param into a separate table from the other columns?

--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN ('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable = False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True, ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-

Rich

Re: [sqlalchemy] best books on DB design for sqlalchemy users?

[Rich Shepard]

On Thu, 23 Jul 2015, Iain Duncan wrote:


I feel like I should really take up my db game for an upcoming set of
projects, and am wondering if there are any real standout books on db
design that fit well with the design philosophy of SQLA. Recos much
appreciated!


Iain,

  Read Joe Celko's books, starting with the latest edition of SQL for
Smarties. I read his columns in Database Advisor and other magazines in the
1980s and have read and used almost all his books. You can't go wrong taking
the time to do it correctly from the gitgo.

Rich

[sqlalchemy] Many-to-Many Table

[Rich Shepard]

  Section 2.1.15 in the 1.0.4 manual describes building an association table
for many-to-many relationships. My application has a table that associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I separate
site and param into a separate table from the other columns?

--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN ('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable = False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True, ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-

Rich

[sqlalchemy] Foreign Keys, Relationships, and Strings

[Rich Shepard]

  I know it's a 3-day holiday for many of us in the US, but ... work needs
to go on for us self-employed folks. :-) So, if I don't see a response until
Tuesday, that's OK.

  In the SQLAlchemy docs I see that some strings are delineated with double
quotes and some with single quotes. For example, when defining classes the
__table_name__ is always single-quoted; when defining a relationsip() the
foreign class name is double-quoted while the target of back_populates is
single-quoted.

  If there's a Python reason for this please make me aware of just what it
is. I've noticed with Python strings in general it matters not which form of
quotation mark I use as long as they're a matched set enclosing the string.

TIA,

Rich

Re: [sqlalchemy] Foreign Keys, Relationships, and Strings

[Rich Shepard]

On Sun, 24 May 2015, Mike Bayer wrote:


there is no reason for one or the other. Python allows both as is
convenient and there's in fact no way to distinguish between string
literals that were created with single or double quotes in any case.


Mike,

  That's what I thought. That's why seeing both in the same line (or
following lines) raised the question.

Carpe holiday,

Rich

Re: [sqlalchemy] relationsip(): In Parent or Child Class?

[Rich Shepard]

On Thu, 21 May 2015, Mike Bayer wrote:


I think the best use case is to put it on both, using back_populates.
This is the focus of current documentation:
http://docs.sqlalchemy.org/en/rel_1_0/orm/backref.html

It's more verbose but I think it's clearer and functionally this is what 
backref does behind the scenes in any case.


Mike,

  OK. I'm carefully working through the ORM tutorial and cleaning code as I
go.

Thanks,

Rich

[sqlalchemy] relationsip(): In Parent or Child Class?

[Rich Shepard]

  There are a number of many-to-one table/class relationships in the
application. In the many class I use ForeignKey() to relate that column to
the appropriate 'one' class and column.

  Reading the ORM tutorial tells me that the relationship() function can be
in either table. I can specify the backref from the many class to the one
class or define it as a child in the one class with the backref to the many
class.

  Is one placement preferred over the other? If not, what criteria are used
to determine where the relationship() function should be placed?

Rich

Re: [sqlalchemy] relationsip(): In Parent or Child Class?

[Rich Shepard]

On Thu, 21 May 2015, Mike Bayer wrote:


I think the best use case is to put it on both, using back_populates.


  Validation check: am I correctly using relationship() in the following set
of three tables? (N.B. Other columns removed for clarity and space saving.)

class Agencies(Base):
__tablename__ = 'agencies'
name = Column(Unicode(48), Sequence('name_seq'), primary_key = True)
...
child1 = relationship('AgencyUnits', back_populates = 'agency', cascade = 
'all, delete, delete-orphan')
child2 = relationship('Permits', back_populates = 'agency', cascade = 'all, 
delete, delete-orphan')
child3 = relationship('Inspect', back_populates = 'agency', cascade = 'all, 
delete, delete-orphan')

class AgencyUnits(Base):
__tablename__ = 'agencyUnits'
name = Column(Unicode(48), Sequence('name_seq'), primary_key = True)
parent_name = Column(Unicode(48), nullable = False, ForeignKey(\
'agencies.name'))
parent_id = relationship(Agency, back_populates = 'units')
...
child = relationship(AgencyContacts, back_populates = 'units', cascade = 
'all, delete, delete-orphan')

class AgencyContacts(Base):
__tablename__ = 'agencyContacts'
contact_id = Column(Integer, Sequence('contact_id_seq'), primary_key=True)
...
unit_id = Column(Unicode(48), ForeignKey('agencyUnits.name'))
parent = relationship(AgencyUnits, back_populates = 'contacts')

Rich

Re: [sqlalchemy] Build With Different Backends [RESOLVED]

[Rich Shepard]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tue, 19 May 2015, Mike Bayer wrote:


Examples: date and time functions are entirely different on both
platforms, schema migration operations e.g. ALTER are generally not
supported on SQLite, SQLite has very different behavior regarding foreign
key constraints (in that they do nothing unless special directives are
emitted per-connection), ...


Mike,

  I wondered about this. It's a scientific application with many date and
time columns in tables and relies on foreign keys extensively to associate
rows in a details table with a specific row in a header table.

A good amount of starting detail is available in the documentation for both 
backends:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html


  Because I'm starting to learn sqlalchemy I'll begin with the sqlite3
version. I should be able to have sqlalchemy generate the tables from the
declarative base .py file so I don't need to create it in advance with
sqlite. The docs mentions that the sqlite version built into python is used.
I presume that I can specify a newer version that's available since the
resultin .db will be packaged with the rest of the application.

Thanks very much,

Rich
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iD8DBQFVW8ypugEIjC001L0RAuPFAJ9ZbhU6S7p5QLsDeQKnJ7mSWr61QACglHhB
XnZZ2otJI7RhxbDohcWPnyM=
=/nLQ
-END PGP SIGNATURE-

[sqlalchemy] Build With Different Backends

[Rich Shepard]

  I thought there was a thread on this but I cannot find it so please point
me to that thread if it exists.

  An application will be provided in two versions: a single-user version
with SQLite3 as the backend, and a multi-user version with PostgreSQL as the
backend.

  Am I correct that the only necessary change in the SQLAlchemy code is the
engine specification?

Rich

Re: [sqlalchemy] foreign key relations

[Rich Shepard]

On Sun, 3 Aug 2014, nathan wrote:


A player and a song are both created separately and associated with a
client.

class Client(Base):
   __tablename__ = 'client'
   id = Column(Integer, primary_key=True)

class Player(Base):
   __tablename__ = 'player'
   id = Column(Integer, primary_key=True)
   clientid = Column(Integer, ForeignKey('client.id'), nullable=False, 
index=True)
   client = relationship('Client', lazy='select')

class Song(Base):
   __tablename__ = 'song'
   id = Column(Integer, primary_key=True)
   clientid = Column(Integer, ForeignKey('client.id'), nullable=False, 
index=True)
   client = relationship('Client', lazy='select')
   name = Column(Unicode(50), nullable=False, index=True)

I now need to modify the above tables/relations so I can assign one song to 
each player.

To accomplish this, would it be ok to add the following to Player?

   songid = Column(Integer, ForeignKey('song.id'))
   song = relationship('Song', lazy='select')

Or maybe I should add an association table for Song and Player?


Nathan,

  If I correctly understand, the relationship between client and song is 1
(client) to many (songs), and the relationship between client and player is
also 1 (client) to many (players). But, you also have the relationship of 1
(song) to 1 (player). Yes?

  This looks similar to my schema that has the class Agency with a 1:Many
relationship with the class Agency_Units, and Agency_Units has a 1:Many
relationship with class Agency_Contacts. Stripped down this is what I have;
I think you can use the same thinking on your schema:

class Agencies(Base):
__tablename__ = 'agencies'

org_name = Column(Unicode(48), Sequence('org_name_seq'), primary_key =
True)
child1 = relationship('Agencies_Units', backref = 'user', cascade =
'all, delete, delete-orphan')


class Agency_Units(Base):
__tablename__ = 'agency_units'

unit_name = Column(Unicode(48), Sequence('unit_name_seq'), primary_key =
True)
ForeignKey('agencies.org_name'))
children = relationship('Agency_Contacts', backref = 'agency_units',
cascade = 'all, delete, delete-orphan')


class Agency_Contacts(Base):
__tablename__ = 'agency_contacts'

id = Column(Integer, Sequence('agency_contact_seq'), primary_key=True)
unit_name = Column(Unicode(48), ForeignKey('agency_units.unit_name'))

  The relationship section of the manual (2.1.10, I believe) will help you
determine the specific relationships of your clients, players, and songs
tables.

HTH,

Rich


--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] foreign key relations

[Rich Shepard]

On Sun, 3 Aug 2014, nathan wrote:


Yes, you have it exactly right. There's currently 1 song to 1 player, but
I'm thinking of going ahead and experimenting with assigning more than 1
song to a player.


Nathan,

  It does not matter if you know the relationship(s) you want you can define
them properly. Remember that it's possible for a table to be a child of a
parent and the parent to a child table.

  The 1:1 relationship is fairly straight-forward. With the 1:Many and
Many:1 it's a bit more complex, but the backref() helps once that's
understood.

  The tricky part is making sure you understand which table is the 1 and
which table is the Many. It's easy to get them backwards.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Foreign Keys and 'backref'

[Rich Shepard]

  On page 23 of the 0.9.7 (Section 2.1.10, Building A Relationship) I see
the example:

class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship(User, backref=backref('addresses', order_by=id))

  The last line is in the child table (with the foreign key) and the
backref() statement has the child table name and an order_by option) Why
is backref written twice, and why is the parent table (user/User) mentioned
twice? Or, is 'user' a column in the addresses table?

  On page 89 (Section 2.3.3, Linking Relationships With Backref) I see a
different example:

class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)

class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))

  Here, the relationship() statement is in the parent table and it specifies
the child class Address with backref mentioned once to the parent table
user.

  These two examples appear different to me. If no one can clear up my
confusion I'll wait for Mike to return from vacation to be straightened out
on how to define referencial integrity.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Foreign Keys and 'backref'

[Rich Shepard]

On Thu, 31 Jul 2014, Jonathan Vanasco wrote:


backref indicates the string name of a property to be placed on the
related mapper’s class that will handle this relationship in the other
direction. The other property will be created automatically when the
mappers are configured. Can also be passed as a backref() object to
control the configuration of the new relationship.

Using your examples side-by-side:

user = relationship(User, backref=backref('addresses', order_by=id))
addresses = relationship(Address, backref=user)


Jonathan,

  So I can specify the relationship from either class/table. I'll read the
docs again to make sure I fully understand which form to use when.

Thank you,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Foreign Keys and 'backref'

[Rich Shepard]

On Thu, 31 Jul 2014, Jonathan Vanasco wrote:


The relevant docs say this (
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#sqlalchemy.orm.relationship.params.backref
)


  Re-reading this section of the docs I'm seeing what I missed the first
time through.

Thanks again, Jonathan,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Foreign Keys and 'backref'

[Rich Shepard]

On Thu, 31 Jul 2014, Jonathan Vanasco wrote:


yes.  `backref` just lets you consolidate defining a relationship in a
single place.



`relationship` lets you specify the relationship from A to B

`backref` is a kwarg to `relationship` that lets you specify the inverse (
B to A )

  class TableA():
   b = relationship(TableB, backref=a)

  class TableB():
  pass

is the same thing as saying:

  class TableA():
   b = relationship(TableB)

  class TableB():
   a = relationship(TableA)

`backref` can either be a `String` OR a `backref()` constructor -- which
lets you customized the join with the same conditions you can elect in the
`relationship` constructor

  class Foo():
   bar = relationship(Bar, lots_of_keywords_here=True,
backref=backref(the_same_keywords_work_here=True) )


Jonathan,

  That makes everything clear.

  My original confusion arose because I'm used to specifying many children
to one parent with the postgres syntax REFERENCES in the children's table;
that's the only relationships I've used (other than many-to-many in linking
tables). The abundance of options in SQLAlchemy threw me.

Much appreciated,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Wed, 30 Jul 2014, Werner wrote:


I don't like using 'name' columns as primary keys I would instead use an
'id' column and would set 'index=True' on the name column.


Werner,

  The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment
serial number) is prefered over an artificial, meaningless, integer key to
prevent duplicate data. See any of Joe Celko's SQL books.

  Over the past 30 or so years I've resorted to artificial keys only when
absolutely necessary. Consider a table for water chemistry constituent
concentrations. There can be no more than one row for the concentration of a
specified constituent from a distinct location on a given day. The only way
to ensure this uniqueness is with the compound primary key of (parameter,
sampdate, site). An articial 'id' column fails to prevent duplication
because someone could enter the same laboratory results more than once and
each row would have a unique 'id' primary key but duplicate data.

  In the early 1990s I was fired from a database consulting assignment with
a medical resarch unit because I changed their flat-file database structure
to a relational schema and turned up duplcate data for a number of patients.
When you consider the effects on published analyses of data that contained
duplicate entries, they had to pick a scapegoat and I was it. :-)

  Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is
the latest) for robust DDL practices.

  The unicode vs string suggestion is interesting. I'm not sure of the
advantages (or disadvantages) but if the change is neutral I'll run a global
search-and-replace.

Thanks,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


Celko's books are great but surrogate integer PKs are an unavoidable
practice within relational databases, they are a requirement of most DBAs
I've dealt with as they perform predictably in terms of indexing and space
requirements, especially considering that a PK implies the format of all
the FKs that will refer to it. Typically a UNIQUE constraint is placed on
the natural key to prevent dupes.


Mike,

  That's interesting. I've not had any issues, but I've not developed many
multi-user, large databases.


In my own experience we actually tried using meaningful UUIDs as primary
keys in a project some years ago and it was an utter disaster. All PK / FK
indexes quadrupled in space and performance suffered terribly. This was on
a Postgresql backend which should have been a better performer in a
non-standard context like that (on a big ol' DB like SQL server, forget
it).


  Wonder if that's been improved in later versions.

  So, do you recommend that surrogate keys be used in all tables, or only on
those that meet certain criteria? I'm always open to learning new things and
improving the work I do.

Regards,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


With that, plus the predictable indexing, I'm always going to use them.
But, I think there's a fair degree of preference still here. With natural
PKs, the biggest issue is how much space indexes are going to take up
considering that everything that FKs to that PK has to mirror out those
same columns.


Mike,

  Fair enough. In the current application I'm developing most tables will
have comparatively few rows ( 500 in most cases). The data tables can
easily have  100,000 rows, but the only table that would relate to those is
the 'changed' table which records what, when, why, and by whom a change was
made. This is for audit trail purposes.

Thanks for your insights,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


Typically a UNIQUE constraint is placed on the natural key to prevent
dupes.


  I can see this when the natural key is a single column, but wonder how a
compound natural key is represented if a serial integer is used as the
surrogate 'id' key. For example,

class Changed_Data(Base):
__tablename__ = changed_data

id = Column(Integer, primary_key = True)
which_table = Column(unicode(32), nullable = False)
which_attrib = Column(unicode(32), nullable = False)
when_changed = Column(Timestamp, nullable = False)
curr_value = Column(Unicode(32), nullable = False)
new_value = Column(Unicode(32), nullable = False)
changed_by = Column(Unicode(32), nullable = False)
reason = Column(Text)

The postgres schema specifies the primary key as (which_table,
which_attribute, when_changed). If I make each of those columns Unique is it
the set of columns that is unique or each individual column?

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


You make a single explicit UniqueConstraint object that specifies all three.


  Thanks, Mike. I missed that in the docs.

  Next question will appear only after a thorough search of your book.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Single and Double Quotes

[Rich Shepard]

  Is there a SQLAlchemy rule defining when single and double quotes are to
be used? I see examples of both in a class, such as this example on page 103
of the 0.9.7 doc:

class Entry(Base):
__tablename__ = 'entry'
entry_id = Column(Integer, primary_key=True)
widget_id = Column(Integer, ForeignKey('widget.widget_id'))
name = Column(String(50))
__table_args__ = (
UniqueConstraint(entry_id, widget_id),
)

  In there the only double quotes are the two arguments to UniqueConstraint
so I assume there's a general rule that I've missed reading.

TIA,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Single and Double Quotes

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


within python, single and double quotes are interchangeable and to the
extent you see them used inconsistently is due to inconsistency on the
part of myself and in some cases other developers who have written these
docs.


Mike,

  I thought that might be the case, but wanted to make sure before I stuck
my foot in my mouth.

Thanks for clarifying,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] vacation...

[Rich Shepard]

On Wed, 30 Jul 2014, Michael Bayer wrote:


I'm on vacation from thursday tomorrow through next thursday, so folks
please hold down the fort! I won't be off the grid but I might not be able
to get to my email as regularly.


mike,

  Have fun, travel safely, and don't worry about all of us.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

  I'm starting to learn SQLAlchemy; have 0.9.7 installed on Slackware-14.1
with Python-2.7.5, wxPython-3.0.0.0, and postgresql-9.3.4.

  1)  In the docs I see a row constraint example using an integer comparison
attrib.CheckConstraint('attrib5'). What is the syntax for a list, such as
this postgres example?

CHECK (agency_name IN ('Federal', 'State', 'County', 'City', 'Local',
'Regional'),

  Do I write agency_name.CheckConstraint('Federal', 'State', 'County',
'City', 'Local', 'Regional')?

  2) I have searched the docs for instructions on how to specify a
multi-column primary key, but have not found an example. The only references
I find to multi-column keys are for foreign keys. My applicaton has several
tables that require multi-column keys.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys [RESOLVED]

[Rich Shepard]

On Tue, 29 Jul 2014, Simon King wrote:


Sorry, I don't know the answer to this, but based on the attrib example,
I would guess that the string is passed directly to the database, so you
would write something like:

agency_name.CheckConstraint(agency_name IN ('Federal', 'State',
'County', 'City', 'Local', 'Regional'))


Simon,

  I saw a single column and value for the example, but did not extrapolate
it the way you do. That should do the trick.


Specify the primary_key=True keyword argument for each column that you
want to form part of the primary key.


  Ah-ha! I did not pick that up from reading the docs.


Hope that helps,


  Certainly does!

Thanks very much,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Tue, 29 Jul 2014, Simon King wrote:


Hope that helps,


Simon, and others:

  As a check that I understand the basics please check the syntax of this
set of three related tables:

class Agencies(Base):
__tablename__ = 'agencies'

org_name = Column(String(48), primary_key = True)
acronym = Column(String(8), value=' ', nullable = False)
org_lvl = Column(String(8), value='State', nullable = False,
CheckConstraint(org_lvl(org_lvl IN ('Federal', 'State', 'County',
'City', 'Local', 'Regional'))
website = Column(String(64), value=' ')
comment = Column(STring)

class Agency_Units(Base):
__tablename__ = 'agency_units'

unit_name = Column(String(48), nullable = False, unique = True, primary_key 
= True)
parent_name = Column(String(48), nullable = False, 
ForeignKey('agencies.org_name'), primary_key = True)

agencies = relationship(Agencies, backref=backref('agency_units'))

acronym = Column(String(8))
addr1 = Column(String(32), nullable = False)
addr2 = Column(String(32))
city = Column(String(16), nullable = False)
state_prov = Column(String(2), nullable = False)
postcode = Column(String(10), nullable = False)
phone = Column(String(10))
fax = Column(String(10))
website = Column(String(64))
comment = Column(String)


class Agency_Contacts(Base):
__tablename__ = 'agency_contacts'

last_name = Column(String(20), nullable = False, primary_key = True)
first_name = Column(String(16), nullable = False, primary_key = True)
mi = Column(String(1))
agency_unit = Column(String(48), nullable = False, primary_key = True,
ForeignKey('agency_units.unit_name'))

agency_unites = relationship(Agency_Units, 
backref=backref('agency_contacts'))

title = Column(String(32))
phone = Column(String(10), nullable = False)
extension = Column(String(6))
email = Column(String(32))
start_date = Column(Date, nullable = False)
end_date = Column(Date)
comments = Column(String)

  I think that's how to express multiple column primary keys and the foreign
references; at least, that's how I interpreted the doc. Getting corrected
now will make life easier in the future.

Thanks in advance,

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys

[Rich Shepard]

On Tue, 29 Jul 2014, Rich Shepard wrote:


   CheckConstraint(org_lvl(org_lvl IN ('Federal', 'State', 'County',
   'City', 'Local', 'Regional'))


  Oops! that first 'org_lvl(' comes out.

Rich

--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.