get to dialect specific column type

[werner]

I am trying to automatically generate the stored procedure I need for
the localize stuff.

So, would like to do something like this:

aninst.__table__.c['created_at'].type.get_dbapi_type(dbapi) - to get
e.g. TIMESTAMP for a DateTime column with Firebird SQL.

What is the most efficient/easy way to get at "dbapi" from e.g. an instance?

Isn't there some more elegant way then doing
"connection.engine.dialect.dbapi"?

Werner

[werner]

I tried with a hack to get to this, but still no luck.

I am doing:

from sqlalchemy.dialects.firebird import dialect
...
fbDialect = dialect()

...
if str(col.type) == 'DATETIME':
print col.type.dialect_impl(fbDialect)
print col.type.get_dbapi_type(fbDialect)

The first one gives me "DATETIME" and the second throws this exception.

Traceback (most recent call last):
File "saCreateDb.py", line 5, in <module>
import model as db
File "C:\dev\aaTests\sqla\i18nFB\model.py", line 116, in <module>
class Country_LV(Base):
File "C:\dev\aaTests\sqla\i18nFB\model.py", line 117, in Country_LV
__table__ = sautils.make_localize_view(Country(), Country_L(),
Language(), metadata)
File "C:\dev\aaTests\sqla\i18nFB\sautils.py", line 181, in
make_localize_view
storedProc = doCreateLocaleStoredProc(baseinst, baseTable,
localeTable, localeLangCol, localeFK, procName)
File "C:\dev\aaTests\sqla\i18nFB\sautils.py", line 31, in
doCreateLocaleStoredProc
print col.type.get_dbapi_type(fbDialect)
File
"c:\python26\lib\site-packages\sqlalchemy-0.6.4-py2.6.egg\sqlalchemy\types.py",
line 1191, in get_dbapi_type
return dbapi.DATETIME
AttributeError: 'FBDialect_kinterbasdb' object has no attribute 'DATETIME'

What am I doing wrong here? And is there a cleaner way of doing this,
i.e. get the dialect currently used instead of using a hard coded dialect.

[Michael Bayer]

what is the piece of information you ultimately want ? I'm not sure what you need the DBAPI type tokens for, unless you are working with the DBAPI's cursor object directly (in which case, you're working with the DBAPI already...dbapi.DATETIME ? if you're generating a stored procedure you're already well within the realm of non-DB-agnostic).

>> Isn't there some more elegant way then doing "connection.engine.dialect.dbapi"?

there's "import kintersbasdb" ....

[werner]

I try to explain in more detail what I am trying to do.

class Country(Base, CreateUpdateMixin):
__tablename__ = u'countries'

id = sa.Column(sa.BigInteger(), sa.Sequence('countries_id'),
primary_key=True, nullable=False)
name = sa.Column(sa.String(length=30, convert_unicode=False))
iso2 = sa.Column(sa.String(length=2, convert_unicode=False))
iso3 = sa.Column(sa.String(length=3, convert_unicode=False))
telcode = sa.Column(sa.SmallInteger())

__localize_columns__ = ['name', ]

class Country_L(Base):
__table__ = sautils.make_localize_table(Country, 'countries_l',
Language, metadata)

language = sao.relation(Language, backref='country_l')
country = sao.relation(Country, backref='country_l')

class Country_LV(Base):

__table__ = sautils.make_localize_view(Country(), Country_L(),
Language(), metadata)

Witin "make_localize_view" I need to generate a stored procedure which
gets information such as columns etc from "Country" and "Country_L" ,
the generated code looks like this:

CREATE OR ALTER PROCEDURE countries_lp
returns (created_at DATE, updated_at TIMESTAMP, id BIGINT, name
VARCHAR(30), iso2 VARCHAR(2), iso3 VARCHAR(3), telcode SMALLINT) as
declare variable locale_name VARCHAR(30);

begin
for select created_at, updated_at, id, name, iso2, iso3, telcode
from countries
into :created_at, :updated_at, :id, :name, :iso2, :iso3, :telcode
do
begin
begin
locale_name = Null;

select name from countries_l
where :id = countries_l.fk_countries_id and
countries_l.fk_languages_code5 = rdb$get_context('USER_SESSION',
'LANG_CODE')
into :name;
end
if (:locale_name is not Null) then
begin
name = :locale_name;
end

suspend;
end
end

part of the code to generate the above is the following:

for col in basetable.c:
if str(col.type) == 'DATETIME':
# hack as I can't figure out a nicer/cleaner way
colType = 'TIMESTAMP'

basetable = Country.__table__

What I like to do is replace the check for "DATETIME" with
similar/same code I assume "meta.create_all(engine)" is using to
generate "create table" (can't yet figure out where/how this is all
done) and ideally this should work not only for Firebird engine.

Hope this is clearer.

Thanks for looking at all this.
Werner

[Michael Bayer]

nothing to do with DBAPI or their types. Call str(col.type) will give you its default compilation. Call str(col.type.compile(dialect=firebird.dialect()) will give you whatever firebird does with those types.

[werner]

Michael,

On 22/09/2010 18:31, Michael Bayer wrote:
...

> nothing to do with DBAPI or their types. Call str(col.type) will give
> you its default compilation. Call
> str(col.type.compile(dialect=firebird.dialect()) will give you
> whatever firebird does with those types.

Thanks that put me on the right track.

Instead of passing the stored procedure to a DDLElement I generate it
within the DDLElement code, which in turn means I can get to the
"dialect" in use, so didn't have to hard code for Firebird.

Makes the localization stuff a bit nicer/cleaner.

Thanks again for your patience and help on all this
Werner