Re: [sqlalchemy] Expressing a SQL DOMAIN as a class
On Mon, Jan 7, 2019 at 7:06 PM Rich Shepard wrote:
>
> 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.
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)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([A(data="CO"), B(data="NY")])
s.commit()
SQL output includes:
CREATE TYPE states AS ENUM ('AK', 'AL', 'CO', 'NY')
CREATE TABLE a (
id SERIAL NOT NULL,
data states,
PRIMARY KEY (id)
)
CREATE TABLE b (
id SERIAL NOT NULL,
data states,
PRIMARY KEY (id)
)
>
> 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 -
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
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
On Mon, Jan 7, 2019 at 5:09 PM Rich Shepard wrote: > > 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. Postgresql has ENUM within CREATE TYPE: https://www.postgresql.org/docs/9.1/datatype-enum.html I'm not sure if this is a "DOMAIN" behind the scenes or what. 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, > > 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 - 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
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
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.
Re: [sqlalchemy] Expressing a SQL DOMAIN as a class
On Mon, Jan 7, 2019 at 4:39 PM Rich Shepard wrote:
>
> 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.
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?
>
> 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.
--
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
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] Column '…' in where clause is ambiguous, on inner join with same table
you should use cursor.callproc() to call this stored procedure. See
https://docs.sqlalchemy.org/en/latest/core/connections.html#calling-stored-procedures
On Mon, Jan 7, 2019 at 10:51 AM matthijs Blaauw
wrote:
>
> I made an MySQL Stored Procedure that is called updateplayerrank. It creates
> a table called that computes a 'dense rank'. I want to store this rank into
> the same table.
>
> DELIMITER $$
> CREATE DEFINER=`root`@`%` PROCEDURE `updateplayerrank`(IN gameidin int)
> BEGIN
> SET @prev_value = NULL;
> SET @rank_count = 0;
> UPDATE player AS p INNER JOIN
> ((SELECT
> id,
> CASE
> WHEN @prev_value = correct THEN @rank_count
> WHEN @prev_value:=correct THEN @rank_count:=@rank_count + 1
> ELSE @rank_count:=@rank_count + 1
> END AS rank
> FROM
> player as x
> WHERE
> x.gameid = gameidin
> ORDER BY correct DESC)) AS d
> ON d.id = p.id
> SET
> p.ranked = d.rank
> WHERE
> p.gameid = gameidin;
> END
> $$ DELIMITER ;
>
> But when I run the stored the stored procedure created using the code above
> with Flask-SQLAlchemy it returns the following error:
>
> (1052, "Column 'gameid' in where clause is ambiguous")
>
> Is there a way that I can stop the ambiguity error? Or is there a better
> Query to do this operation. I am using MySQL version 5.7, so I don't have
> acces to winnow functions. I am running the Stored procedure using the
> following code:
>
> db.engine.execute("CALL updateplayerrank(%s)", (gameid))
>
> I made the Stored Procedure With MySQLWorkbench and it runs fine over there.
> I only get the error with Flask-SQLAlchemy
>
> --
> 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 -
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 '…' in where clause is ambiguous, on inner join with same table
I made an MySQL Stored Procedure that is called updateplayerrank. It
creates a table called that computes a 'dense rank'. I want to store this
rank into the same table.
DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `updateplayerrank`(IN gameidin
int) BEGIN
SET @prev_value = NULL;
SET @rank_count = 0;
UPDATE player AS p INNER JOIN
((SELECT
id,
CASE
WHEN @prev_value = correct THEN @rank_count
WHEN @prev_value:=correct THEN @rank_count:=@rank_count + 1
ELSE @rank_count:=@rank_count + 1
END AS rank
FROM
player as x
WHERE
x.gameid = gameidin
ORDER BY correct DESC)) AS d
ON d.id = p.id
SET
p.ranked = d.rank
WHERE
p.gameid = gameidin; END$$ DELIMITER ;
But when I run the stored the stored procedure created using the code above
with Flask-SQLAlchemy it returns the following error:
(1052, "Column 'gameid' in where clause is ambiguous")
Is there a way that I can stop the ambiguity error? Or is there a better
Query to do this operation. I am using MySQL version 5.7, so I don't have
acces to winnow functions. I am running the Stored procedure using the
following code:
db.engine.execute("CALL updateplayerrank(%s)", (gameid))
I made the Stored Procedure With MySQLWorkbench and it runs fine over
there. I only get the error with Flask-SQLAlchemy
--
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.
