[sqlalchemy] Re: Apache Beam SQLAlchemy PyMySql?
I think my issue is I just didn't have mysql+pymysql :facepalm: . Will
continue to update in case others want to do this.
On Saturday, May 18, 2019 at 5:40:58 PM UTC-4, mkwyche wrote:
>
> Hi,
>
> I'm trying to run a Beam Pipeline with SQLAlchemy. Google DataFlowRunner
> doesn't allow me to add MySQLdb to my pipeline running on the cloud. You
> can see a description of the issue here
> https://stackoverflow.com/questions/56202734/python-mysql-in-cloud-dataflowrunner
> .
> It seems like every time I try to create an engine the engine requires
> `MySQLdb`
>
> ```
> Traceback (most recent call last):
> File "phy/uploader/season_stat_calculator/pipeline.py", line 10, in
>
> from phy.uploader.season_stat_calculator.upload_season_stats_rocky
> import get_batters_for_season, \
> File
> "/Users/mkwyche/workspace/python/phy/uploader/season_stat_calculator/upload_season_stats_rocky.py",
>
> line 12, in
> from phy.shared.models.season_stats.season_stats import SeasonStats
> File "/Users/mkwyche/workspace/python/phy/shared/models/__init__.py",
> line 19, in
> _ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA,
> echo=SQL_DEBUG)
> File
> "/Users/mkwyche/workspace/python/phy/shared/utils/sqlalchemy_utils.py",
> line 22, in get_mysql_engine
> return create_engine(db_url, echo=echo, pool_recycle=1000)
> File
> "/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/engine/__init__.py",
>
> line 435, in create_engine
> return strategy.create(*args, **kwargs)
> File
> "/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py",
>
> line 87, in create
> dbapi = dialect_cls.dbapi(**dbapi_args)
> File
> "/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py",
>
> line 118, in dbapi
> return __import__("MySQLdb")
> ImportError: No module named MySQLdb
> ```
>
> Is there anyway around needing MySQLdb when using SQLAlchemy?
>
> Thanks,
>
> Marcus Wyche
>
--
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/5e737468-af5d-4c3f-ba47-3c6f179d7fc3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Apache Beam SQLAlchemy PyMySql?
Hi,
I'm trying to run a Beam Pipeline with SQLAlchemy. Google DataFlowRunner
doesn't allow me to add MySQLdb to my pipeline running on the cloud. You
can see a description of the issue here
https://stackoverflow.com/questions/56202734/python-mysql-in-cloud-dataflowrunner
.
It seems like every time I try to create an engine the engine requires
`MySQLdb`
```
Traceback (most recent call last):
File "phy/uploader/season_stat_calculator/pipeline.py", line 10, in
from phy.uploader.season_stat_calculator.upload_season_stats_rocky
import get_batters_for_season, \
File
"/Users/mkwyche/workspace/python/phy/uploader/season_stat_calculator/upload_season_stats_rocky.py",
line 12, in
from phy.shared.models.season_stats.season_stats import SeasonStats
File "/Users/mkwyche/workspace/python/phy/shared/models/__init__.py",
line 19, in
_ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA, echo=SQL_DEBUG)
File
"/Users/mkwyche/workspace/python/phy/shared/utils/sqlalchemy_utils.py",
line 22, in get_mysql_engine
return create_engine(db_url, echo=echo, pool_recycle=1000)
File
"/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/engine/__init__.py",
line 435, in create_engine
return strategy.create(*args, **kwargs)
File
"/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py",
line 87, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File
"/Users/mkwyche/workspace/python/.beam_env/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py",
line 118, in dbapi
return __import__("MySQLdb")
ImportError: No module named MySQLdb
```
Is there anyway around needing MySQLdb when using SQLAlchemy?
Thanks,
Marcus Wyche
--
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/b20a22f4-5cc7-4191-adf4-b8ab5abba4c6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Order By Relationship Set Order Type Desc
Looks like you can add `.desc()` after the column name :-D On Monday, April 29, 2019 at 3:40:24 PM UTC-4, mkwyche wrote: > > Looking through the docs and I see there's an `order_by` attribute on > relationships( > https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship). > > How would I order by desc instead of asc? > -- 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] Order By Relationship Set Order Type Desc
Looking through the docs and I see there's an `order_by` attribute on relationships( https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship). How would I order by desc instead of asc? -- 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: Foreign Key Constraint In Different Database Support
Awesome thanks again really appreciate it!
On Wednesday, March 20, 2019 at 5:58:44 PM UTC-4, Mike Bayer wrote:
>
> On Wed, Mar 20, 2019 at 5:52 PM mkwyche >
> wrote:
> >
> > Thanks for the help. Is it best practice to use one declarative_base for
> all models and set the schema on any Models that reference separate
> databases?
>
> I think people usually use one declarative_base shared for all the
> classes in their application because they usually consider all those
> classes against one big object graph with relationships between them.
> But there are certainly useful cases to use multiple declarative bases
> also, which doesn't prevent you from having relationships between them
> either.
>
> For example I didn't show you this but you *can* associate a schema
> with a declarative_base via the metadata:
>
> A = declarative_base(metadata=MetaData(schema="test_schema"))
> B = declarative_base(metadata=MetaData(schema="test_schema_2"))
>
>
> class A1(A):
> __tablename__ = 'a'
>
> id = Column(Integer, primary_key=True)
> data = Column(String)
> bs = relationship("B1")
>
>
> class B1(B):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
> a_id = Column(ForeignKey(A1.id))
> data = Column(String)
>
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> A.metadata.create_all(e)
> B.metadata.create_all(e)
>
> gives you:
>
> CREATE TABLE test_schema.a (
> id SERIAL NOT NULL,
> data VARCHAR,
> PRIMARY KEY (id)
> )
>
>
> CREATE TABLE test_schema_2.b (
> id SERIAL NOT NULL,
> a_id INTEGER,
> data VARCHAR,
> PRIMARY KEY (id),
> FOREIGN KEY(a_id) REFERENCES test_schema.a (id)
> )
>
>
> not like I'd do it that way, but that's an option.
>
>
>
>
>
>
>
> >
> > On Wednesday, March 20, 2019 at 5:14:30 PM UTC-4, Mike Bayer wrote:
> >>
> >> the biggest issue is that you are being confused by the ".bind"
> >> argument to MetaData, in that it suggests the MetaData would know
> >> about the default schema name of the engine which is not the case.
> >> The .bind argument will be going away in a future release for reasons
> >> like this.
> >>
> >> On Wed, Mar 20, 2019 at 2:23 PM mkwyche wrote:
> >> >
> >> > I think I see my biggest issue now. I thought that I needed multiple
> `declarative_base` for each database but I can use the same one for all
> models and specify schema.
> >> >
> >> > On Wednesday, March 20, 2019 at 1:02:47 PM UTC-4, mkwyche wrote:
> >> >>
> >> >> Here's the code I'm using to create `declarative_base`:
> >> >>
> >> >> ```
> >> >>
> >> >> PHIL_DATA = "phil_data"
> >> >> PHIL_DATA3 = "phil_data3"
> >> >> MESA = "mesa"
> >> >> SQL_DEBUG = bool(os.getenv("SQL_DEBUG", False))
> >> >> _ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA,
> echo=SQL_DEBUG)
> >> >> _ENGINE_PHIL_DATA3 = get_mysql_engine(database=PHIL_DATA3,
> echo=SQL_DEBUG)
> >> >> _ENGINE_MESA = get_mysql_engine(database=MESA, echo=SQL_DEBUG)
> >> >>
> >> >> # Set up base contstants
> >> >> PHIL_DATA3_BASE =
> declarative_base(bind=_ENGINE_PHIL_DATA3.connect())
> >> >> PHIL_DATA_BASE = declarative_base(bind=_ENGINE_PHIL_DATA.connect())
> >> >> MESA_BASE = declarative_base(bind=_ENGINE_MESA.connect())
> >> >> ```
> >> >>
> >> >> That base is then used to create their models:
> >> >>
> >> >> ```
> >> >>
> >> >> class PlayerPro(MESA_BASE):
> >> >> ''' player_pro model '''
> >> >> __tablename__ = "player_pro"
> >> >>
> >> >> # Primary Keys
> >> >> ebis_id = Column(MEDIUMINT(9), primary_key=True)
> >> >> bam_id = Column(MEDIUMINT(9), index=True)
> >> >> phil_id = Column(MEDIUMINT(9), index=True)
> >> >>
> >> >>
> >> >>
> >> >> class EvalProHit(PHIL_DATA_BASE):
> >> >> ''' Eval Pro Hit model '''
> >> >> __tablename__ = "eval_pro_hit"
> >> >>
> >> >> def list_id_default(self): # pyli
Re: [sqlalchemy] Re: Foreign Key Constraint In Different Database Support
Thanks for the help. Is it best practice to use one declarative_base for
all models and set the schema on any Models that reference separate
databases?
On Wednesday, March 20, 2019 at 5:14:30 PM UTC-4, Mike Bayer wrote:
>
> the biggest issue is that you are being confused by the ".bind"
> argument to MetaData, in that it suggests the MetaData would know
> about the default schema name of the engine which is not the case.
> The .bind argument will be going away in a future release for reasons
> like this.
>
> On Wed, Mar 20, 2019 at 2:23 PM mkwyche >
> wrote:
> >
> > I think I see my biggest issue now. I thought that I needed multiple
> `declarative_base` for each database but I can use the same one for all
> models and specify schema.
> >
> > On Wednesday, March 20, 2019 at 1:02:47 PM UTC-4, mkwyche wrote:
> >>
> >> Here's the code I'm using to create `declarative_base`:
> >>
> >> ```
> >>
> >> PHIL_DATA = "phil_data"
> >> PHIL_DATA3 = "phil_data3"
> >> MESA = "mesa"
> >> SQL_DEBUG = bool(os.getenv("SQL_DEBUG", False))
> >> _ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA,
> echo=SQL_DEBUG)
> >> _ENGINE_PHIL_DATA3 = get_mysql_engine(database=PHIL_DATA3,
> echo=SQL_DEBUG)
> >> _ENGINE_MESA = get_mysql_engine(database=MESA, echo=SQL_DEBUG)
> >>
> >> # Set up base contstants
> >> PHIL_DATA3_BASE = declarative_base(bind=_ENGINE_PHIL_DATA3.connect())
> >> PHIL_DATA_BASE = declarative_base(bind=_ENGINE_PHIL_DATA.connect())
> >> MESA_BASE = declarative_base(bind=_ENGINE_MESA.connect())
> >> ```
> >>
> >> That base is then used to create their models:
> >>
> >> ```
> >>
> >> class PlayerPro(MESA_BASE):
> >> ''' player_pro model '''
> >> __tablename__ = "player_pro"
> >>
> >> # Primary Keys
> >> ebis_id = Column(MEDIUMINT(9), primary_key=True)
> >> bam_id = Column(MEDIUMINT(9), index=True)
> >> phil_id = Column(MEDIUMINT(9), index=True)
> >>
> >>
> >>
> >> class EvalProHit(PHIL_DATA_BASE):
> >> ''' Eval Pro Hit model '''
> >> __tablename__ = "eval_pro_hit"
> >>
> >> def list_id_default(self): # pylint: disable=no-self-use
> >> return uuid.uuid4().hex
> >>
> >> # Keys
> >> eval_id = Column(VARCHAR(255), primary_key=True,
> default=list_id_default)
> >> phil_id = Column(MEDIUMINT(9), nullable=False)
> >>
> >> ForeignKeyConstraint([phil_id],
> >> [PlayerPro.phil_id],
> >> onupdate="CASCADE")
> >>
> >> ```
> >>
> >> Prior to adding Schema as a table_args to PlayerPro model the creation
> of the foreign key didn't add `mesa.player_pro.phil_id` and instead was
> using `player_pro.phil_id? Adding schema makes things
> >>
> >> work. But I'm wondering why that info couldn't have been derived from
> the base associated with the actual model.
> >>
> >>
> >> On Wednesday, March 20, 2019 at 9:55:44 AM UTC-4, Mike Bayer wrote:
> >>>
> >>> On Wed, Mar 20, 2019 at 7:43 AM mkwyche wrote:
> >>> >
> >>> > For anyone else that runs into a similar issue. This
> https://stackoverflow.com/questions/43584453/how-to-handle-cross-schema-foreign-key-in-sqlalchemy
>
> was helpful.
> >>> >
> >>> > I needed to add
> >>> > ```
> >>> > __table_args__ = {'schema': 'mesa'}
> >>> > ```
> >>> >
> >>> > To `player_pro` model. Then it worked. Why isn't the schema derived
> from the `declaritive_base` associated with the model?
> >>>
> >>> How is the schema being associated with the declarative base ?I
> >>> don't see any mention of "schema" until your example here.
> >>>
> >>>
> >>> >
> >>> > On Wednesday, March 20, 2019 at 7:16:06 AM UTC-4, mkwyche wrote:
> >>> >>
> >>> >> Also I tried to put in their mesa.player_pro.phil_Id instead of the
> actual column and I get this error:
> >>> >>
> >>> >> ```
> >>> >> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
> column 'eval_pro_hit.phil_id'
Re: [sqlalchemy] Re: Foreign Key Constraint In Different Database Support
I think I see my biggest issue now. I thought that I needed multiple
`declarative_base` for each database but I can use the same one for all
models and specify schema.
On Wednesday, March 20, 2019 at 1:02:47 PM UTC-4, mkwyche wrote:
>
> Here's the code I'm using to create `declarative_base`:
>
> ```
>
> PHIL_DATA = "phil_data"
> PHIL_DATA3 = "phil_data3"
> MESA = "mesa"
> SQL_DEBUG = bool(os.getenv("SQL_DEBUG", False))
> _ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA, echo=SQL_DEBUG)
> _ENGINE_PHIL_DATA3 = get_mysql_engine(database=PHIL_DATA3, echo=SQL_DEBUG)
> _ENGINE_MESA = get_mysql_engine(database=MESA, echo=SQL_DEBUG)
>
> # Set up base contstants
> PHIL_DATA3_BASE = declarative_base(bind=_ENGINE_PHIL_DATA3.connect())
> PHIL_DATA_BASE = declarative_base(bind=_ENGINE_PHIL_DATA.connect())
> MESA_BASE = declarative_base(bind=_ENGINE_MESA.connect())
> ```
>
> That base is then used to create their models:
>
> ```
>
> class PlayerPro(MESA_BASE):
> ''' player_pro model '''
> __tablename__ = "player_pro"
>
> # Primary Keys
> ebis_id = Column(MEDIUMINT(9), primary_key=True)
> bam_id = Column(MEDIUMINT(9), index=True)
> phil_id = Column(MEDIUMINT(9), index=True)
>
>
>
> class EvalProHit(PHIL_DATA_BASE):
> ''' Eval Pro Hit model '''
> __tablename__ = "eval_pro_hit"
>
> def list_id_default(self): # pylint: disable=no-self-use
> return uuid.uuid4().hex
>
> # Keys
> eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default)
> phil_id = Column(MEDIUMINT(9), nullable=False)
>
> ForeignKeyConstraint([phil_id],
> [PlayerPro.phil_id],
> onupdate="CASCADE")
>
> ``` Prior to adding Schema as a table_args to PlayerPro model the creation
> of the foreign key didn't add `mesa.player_pro.phil_id` and instead was
> using `player_pro.phil_id? Adding schema makes things
>
> work. But I'm wondering why that info couldn't have been derived from the
> base associated with the actual model.
>
>
> On Wednesday, March 20, 2019 at 9:55:44 AM UTC-4, Mike Bayer wrote:
>>
>> On Wed, Mar 20, 2019 at 7:43 AM mkwyche wrote:
>> >
>> > For anyone else that runs into a similar issue. This
>> https://stackoverflow.com/questions/43584453/how-to-handle-cross-schema-foreign-key-in-sqlalchemy
>>
>> was helpful.
>> >
>> > I needed to add
>> > ```
>> > __table_args__ = {'schema': 'mesa'}
>> > ```
>> >
>> > To `player_pro` model. Then it worked. Why isn't the schema derived
>> from the `declaritive_base` associated with the model?
>>
>> How is the schema being associated with the declarative base ?I
>> don't see any mention of "schema" until your example here.
>>
>>
>> >
>> > On Wednesday, March 20, 2019 at 7:16:06 AM UTC-4, mkwyche wrote:
>> >>
>> >> Also I tried to put in their mesa.player_pro.phil_Id instead of the
>> actual column and I get this error:
>> >>
>> >> ```
>> >> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
>> column 'eval_pro_hit.phil_id' could not find table 'mesa.player_pro' with
>> which to generate a foreign key to target column 'phil_id'
>> >> ```
>> >>
>> >> Is there a way to reference a table from another DeclaritiveBase?
>> >>
>> >> On Wednesday, March 20, 2019 at 7:11:06 AM UTC-4, mkwyche wrote:
>> >>>
>> >>> Hi,
>> >>>
>> >>> I have two classes:
>> >>>
>> >>> ```python
>> >>>
>> >>> class PlayerPro(MESA_BASE):
>> >>> ''' player_pro model '''
>> >>> __tablename__ = "player_pro"
>> >>>
>> >>> # Primary Keys
>> >>> ebis_id = Column(MEDIUMINT(9), primary_key=True)
>> >>> bam_id = Column(MEDIUMINT(9), index=True)
>> >>> phil_id = Column(MEDIUMINT(9), index=True)
>> >>>
>> >>>
>> >>>
>> >>> class EvalProHit(PHIL_DATA_BASE):
>> >>> ''' Eval Pro Hit model '''
>> >>> __tablename__ = "eval_pro_hit"
>> >>>
>> >>> def list_id_default(self): # pylint: disable=no-self-use
>> >>> return uuid.uuid4().hex
>> >>>
>> >>> # Key
Re: [sqlalchemy] Re: Foreign Key Constraint In Different Database Support
Here's the code I'm using to create `declarative_base`:
```
PHIL_DATA = "phil_data"
PHIL_DATA3 = "phil_data3"
MESA = "mesa"
SQL_DEBUG = bool(os.getenv("SQL_DEBUG", False))
_ENGINE_PHIL_DATA = get_mysql_engine(database=PHIL_DATA, echo=SQL_DEBUG)
_ENGINE_PHIL_DATA3 = get_mysql_engine(database=PHIL_DATA3, echo=SQL_DEBUG)
_ENGINE_MESA = get_mysql_engine(database=MESA, echo=SQL_DEBUG)
# Set up base contstants
PHIL_DATA3_BASE = declarative_base(bind=_ENGINE_PHIL_DATA3.connect())
PHIL_DATA_BASE = declarative_base(bind=_ENGINE_PHIL_DATA.connect())
MESA_BASE = declarative_base(bind=_ENGINE_MESA.connect())
```
That base is then used to create their models:
```
class PlayerPro(MESA_BASE):
''' player_pro model '''
__tablename__ = "player_pro"
# Primary Keys
ebis_id = Column(MEDIUMINT(9), primary_key=True)
bam_id = Column(MEDIUMINT(9), index=True)
phil_id = Column(MEDIUMINT(9), index=True)
class EvalProHit(PHIL_DATA_BASE):
''' Eval Pro Hit model '''
__tablename__ = "eval_pro_hit"
def list_id_default(self): # pylint: disable=no-self-use
return uuid.uuid4().hex
# Keys
eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default)
phil_id = Column(MEDIUMINT(9), nullable=False)
ForeignKeyConstraint([phil_id],
[PlayerPro.phil_id],
onupdate="CASCADE")
``` Prior to adding Schema as a table_args to PlayerPro model the creation
of the foreign key didn't add `mesa.player_pro.phil_id` and instead was
using `player_pro.phil_id? Adding schema makes things
work. But I'm wondering why that info couldn't have been derived from the base
associated with the actual model.
On Wednesday, March 20, 2019 at 9:55:44 AM UTC-4, Mike Bayer wrote:
>
> On Wed, Mar 20, 2019 at 7:43 AM mkwyche >
> wrote:
> >
> > For anyone else that runs into a similar issue. This
> https://stackoverflow.com/questions/43584453/how-to-handle-cross-schema-foreign-key-in-sqlalchemy
>
> was helpful.
> >
> > I needed to add
> > ```
> > __table_args__ = {'schema': 'mesa'}
> > ```
> >
> > To `player_pro` model. Then it worked. Why isn't the schema derived from
> the `declaritive_base` associated with the model?
>
> How is the schema being associated with the declarative base ?I
> don't see any mention of "schema" until your example here.
>
>
> >
> > On Wednesday, March 20, 2019 at 7:16:06 AM UTC-4, mkwyche wrote:
> >>
> >> Also I tried to put in their mesa.player_pro.phil_Id instead of the
> actual column and I get this error:
> >>
> >> ```
> >> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
> column 'eval_pro_hit.phil_id' could not find table 'mesa.player_pro' with
> which to generate a foreign key to target column 'phil_id'
> >> ```
> >>
> >> Is there a way to reference a table from another DeclaritiveBase?
> >>
> >> On Wednesday, March 20, 2019 at 7:11:06 AM UTC-4, mkwyche wrote:
> >>>
> >>> Hi,
> >>>
> >>> I have two classes:
> >>>
> >>> ```python
> >>>
> >>> class PlayerPro(MESA_BASE):
> >>> ''' player_pro model '''
> >>> __tablename__ = "player_pro"
> >>>
> >>> # Primary Keys
> >>> ebis_id = Column(MEDIUMINT(9), primary_key=True)
> >>> bam_id = Column(MEDIUMINT(9), index=True)
> >>> phil_id = Column(MEDIUMINT(9), index=True)
> >>>
> >>>
> >>>
> >>> class EvalProHit(PHIL_DATA_BASE):
> >>> ''' Eval Pro Hit model '''
> >>> __tablename__ = "eval_pro_hit"
> >>>
> >>> def list_id_default(self): # pylint: disable=no-self-use
> >>> return uuid.uuid4().hex
> >>>
> >>> # Keys
> >>> eval_id = Column(VARCHAR(255), primary_key=True,
> default=list_id_default)
> >>> phil_id = Column(MEDIUMINT(9), nullable=False)
> >>>
> >>> ForeignKeyConstraint([phil_id],
> >>> [PlayerPro.phil_id],
> >>> onupdate="CASCADE")
> >>>
> >>> ```
> >>>
> >>> There in two different databases. When I go to create the tables the
> create statement is incorrect:
> >>>
> >>> ```sql
> >>> INFO:sqlalchemy.engine.base.Engine:
> >>> CREATE TABLE eval_pro_hit (
> >>> e
[sqlalchemy] Re: Foreign Key Constraint In Different Database Support
For anyone else that runs into a similar issue. This
https://stackoverflow.com/questions/43584453/how-to-handle-cross-schema-foreign-key-in-sqlalchemy
was helpful.
I needed to add
```
__table_args__ = {'schema': 'mesa'}
```
To `player_pro` model. Then it worked. Why isn't the schema derived from
the `declaritive_base` associated with the model?
On Wednesday, March 20, 2019 at 7:16:06 AM UTC-4, mkwyche wrote:
>
> Also I tried to put in their mesa.player_pro.phil_Id instead of the actual
> column and I get this error:
>
> ```
> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column
> 'eval_pro_hit.phil_id' could not find table 'mesa.player_pro' with which to
> generate a foreign key to target column 'phil_id'
> ```
>
> Is there a way to reference a table from another DeclaritiveBase?
>
> On Wednesday, March 20, 2019 at 7:11:06 AM UTC-4, mkwyche wrote:
>>
>> Hi,
>>
>> I have two classes:
>>
>> ```python
>>
>> class PlayerPro(MESA_BASE):
>> ''' player_pro model '''
>> __tablename__ = "player_pro"
>>
>> # Primary Keys
>> ebis_id = Column(MEDIUMINT(9), primary_key=True)
>> bam_id = Column(MEDIUMINT(9), index=True)
>> phil_id = Column(MEDIUMINT(9), index=True)
>>
>>
>>
>> class EvalProHit(PHIL_DATA_BASE):
>> ''' Eval Pro Hit model '''
>> __tablename__ = "eval_pro_hit"
>>
>> def list_id_default(self): # pylint: disable=no-self-use
>> return uuid.uuid4().hex
>>
>> # Keys
>> eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default)
>> phil_id = Column(MEDIUMINT(9), nullable=False)
>>
>> ForeignKeyConstraint([phil_id],
>> [PlayerPro.phil_id],
>> onupdate="CASCADE")
>>
>> ```
>>
>> There in two different databases. When I go to create the tables the
>> create statement is incorrect:
>>
>> ```sql
>> INFO:sqlalchemy.engine.base.Engine:
>> CREATE TABLE eval_pro_hit (
>> eval_id VARCHAR(255) NOT NULL,
>> phil_id MEDIUMINT(9) NOT NULL,
>>
>> FOREIGN KEY(phil_id) REFERENCES player_pro (phil_id) ON UPDATE CASCADE
>> )
>> ```
>>
>> Should be mesa.player_pro instead of player_pro. This is occuring from
>> PHIL_DATA_BASE.metadata.create_all() . How can I accomplish this with
>> models?
>>
>
--
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] Re: Foreign Key Constraint In Different Database Support
Also I tried to put in their mesa.player_pro.phil_Id instead of the actual column and I get this error: ``` sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'eval_pro_hit.phil_id' could not find table 'mesa.player_pro' with which to generate a foreign key to target column 'phil_id' ``` Is there a way to reference a table from another DeclaritiveBase? On Wednesday, March 20, 2019 at 7:11:06 AM UTC-4, mkwyche wrote: > > Hi, > > I have two classes: > > ```python > > class PlayerPro(MESA_BASE): > ''' player_pro model ''' > __tablename__ = "player_pro" > > # Primary Keys > ebis_id = Column(MEDIUMINT(9), primary_key=True) > bam_id = Column(MEDIUMINT(9), index=True) > phil_id = Column(MEDIUMINT(9), index=True) > > > > class EvalProHit(PHIL_DATA_BASE): > ''' Eval Pro Hit model ''' > __tablename__ = "eval_pro_hit" > > def list_id_default(self): # pylint: disable=no-self-use > return uuid.uuid4().hex > > # Keys > eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default) > phil_id = Column(MEDIUMINT(9), nullable=False) > > ForeignKeyConstraint([phil_id], > [PlayerPro.phil_id], > onupdate="CASCADE") > > ``` > > There in two different databases. When I go to create the tables the > create statement is incorrect: > > ```sql > INFO:sqlalchemy.engine.base.Engine: > CREATE TABLE eval_pro_hit ( > eval_id VARCHAR(255) NOT NULL, > phil_id MEDIUMINT(9) NOT NULL, > > FOREIGN KEY(phil_id) REFERENCES player_pro (phil_id) ON UPDATE CASCADE > ) > ``` > > Should be mesa.player_pro instead of player_pro. This is occuring from > PHIL_DATA_BASE.metadata.create_all() . How can I accomplish this with > models? > -- 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] Foreign Key Constraint In Different Database Support
Hi, I have two classes: ```python class PlayerPro(MESA_BASE): ''' player_pro model ''' __tablename__ = "player_pro" # Primary Keys ebis_id = Column(MEDIUMINT(9), primary_key=True) bam_id = Column(MEDIUMINT(9), index=True) phil_id = Column(MEDIUMINT(9), index=True) class EvalProHit(PHIL_DATA_BASE): ''' Eval Pro Hit model ''' __tablename__ = "eval_pro_hit" def list_id_default(self): # pylint: disable=no-self-use return uuid.uuid4().hex # Keys eval_id = Column(VARCHAR(255), primary_key=True, default=list_id_default) phil_id = Column(MEDIUMINT(9), nullable=False) ForeignKeyConstraint([phil_id], [PlayerPro.phil_id], onupdate="CASCADE") ``` There in two different databases. When I go to create the tables the create statement is incorrect: ```sql INFO:sqlalchemy.engine.base.Engine: CREATE TABLE eval_pro_hit ( eval_id VARCHAR(255) NOT NULL, phil_id MEDIUMINT(9) NOT NULL, FOREIGN KEY(phil_id) REFERENCES player_pro (phil_id) ON UPDATE CASCADE ) ``` Should be mesa.player_pro instead of player_pro. This is occuring from PHIL_DATA_BASE.metadata.create_all() . How can I accomplish this with models? -- 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.
