Pfthb

I have a sqlalchemy table defined like so

from sqlalchemy.sql.schema import Table

my_table = Table(
 "my_table",
 metadata,
 Column("my_id", BigInteger(), primary_key=True),
 ...
)

I am trying to inspect this instance to get the module where it was created. I have tried using sqlalchemy.inspect(my_table).__module__, my_table.__module__, and inspect.getmodule(my_table) however, all three return sqlalchemy.sql.schema which is the module where Table is defined rather than where my_table is defined.

How can I retrieve the name of the module where I have instantiated my_table?

You could add the functionality by subclassing Table. In SQLAlchemy, Table specifically overrides Table.__init__() to make it a no-op:

def __init__(self, *args, **kw):
 """Constructor for :class:`~.schema.Table`.
 This method is a no-op. See the top-level
 documentation for :class:`~.schema.Table`
 for constructor arguments.
 """
 # __init__ is overridden to prevent __new__ from
 # calling the superclass constructor.

The key being that it does not invoke super().__init__(), so that sqlalchemy can take command of instantiation and whatever you do, that needs to be maintained.

from sqlalchemy.sql.schema import Table

class MyTable(Table):

 def __init__(self, *args, **kwargs):
 self._where_am_i = __file__

my_table = MyTable(
 "my_table",
 metadata,
 Column("my_id", BigInteger(), primary_key=True)
)

In this case, MyTable.__init__() is still blocking the superclass constructor, but it also adds an attribute to the instance which will be the name of the module that the class is instantiated within. I specifically chose an obscure attribute name (_where_am_i) that is unlikely to be overwritten by sqlalchemy and using __file__ returns the path of the module (but you can make that anything you want).

I tested that inserts and selects still work:

import logging
from sqlalchemy.sql import select
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig(level=logging.INFO)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
conn = engine.connect()
conn.execute(my_table.insert(), ["my_id": i for i in range(1, 6)])
s = select([my_table])
result = conn.execute(s)
for row in result:
 print(row)
# (1,)
# (2,)
# (3,)
# (4,)
# (5,)

And instantiation location:

print(my_table._where_am_i) # 53302898.py (that's the name of my module).

External module:

# external_module.py

from sqlalchemy_app import Base
from sqlalchemy.sql.schema import Table
from sqlalchemy import Column, BigInteger

metadata = Base.metadata

class MyTable(Table):

 def __init__(self, *args, **kwargs):
 self._where_am_i = __file__

my_table = MyTable(
 "my_table",
 metadata,
 Column("my_id", BigInteger(), primary_key=True)
)

And:

# 53302898.py

from external_module import my_table

if __name__ == '__main__':
 print(my_table._where_am_i) # prints C:Userspeter_000OneDrivegittestexternal_module.py

Note how it returned the relative file path in the first test and the absolute file path in the external module test. You can read about that here: Python __file__ attribute absolute or relative? but you can make that _where_am_i attribute return whatever you need to suit your application.

EDIT
The above solution requires subclassing the Table class inside the module where instances are formed, otherwise it will peg the module where the Class is instantiated, not the instances. If you only want to subclass Table once in your project you'd need to pass the location to the constructor.

This works:

class MyTable(Table):

 def __init__(self, *args, _where_am_i=None, **kwargs):
 self._where_am_i = _where_am_i

...but you get a warning upon instantiation:

SAWarning: Can't validate argument '_where_am_i'; can't locate any SQLAlchemy dialect named '_where'.

To avoid that, you'd have to override sqlalchemy's alternate constructor, Table._init(), strip out the location parameter and then delegate back up the chain:

class MyTable(Table):

 def _init(self, *args, _where_am_i=None, **kwargs):
 self._where_am_i = _where_am_i
 super()._init(*args, **kwargs)

Import from external module:

# 53302898.py
from external_module import MyTable

my_table = MyTable(
 "my_table",
 metadata,
 Column("my_id", BigInteger(), primary_key=True),
 _where_am_i = __file__
)

if __name__ == '__main__':
 print(my_table._where_am_i) # 53302898.py

All above tests still pass.

You can't. If you refer to the Python documentation index, you see that there are three entries for __module__: one for a class attribute, one for a function attribute, and one for a method attribute. Only those types of objects have the module in which they were declared recorded. my_table is none of these; it's just an instance of the Table class, so the only __module__ you can find on it is Table.__module__.