these types as far as sqlite are concerned are kind of semi-“fake”, they’re just lots of names that all end up having the same effect of an “affinity”, and even that is not very strong as you can still put a text string into these columns. Sqlite gives us no way to just get at the simple “pragma rules” associated with the type names, the docs just have this vague statement "This table shows only a small subset of the datatype names that SQLite will accept.” - OK great wheres *all* the names, or a function that can give us the affinity rule number? there is none. so we are stuck guessing (unless someone wants to read the C source).
so anyway, “DOUBLE” should be added however you can add this on your own like this: from sqlalchemy.dialects.sqlite import base from sqlalchemy.types import DOUBLE base.ischema_names[‘DOUBLE’] = DOUBLE On Jan 28, 2014, at 9:38 PM, Erich Blume <[email protected]> wrote: > Some additional information - a GUI tool for inspecting sqlite databases > tells me that the following is the CREATE syntax for such a table as I > mention above: > > CREATE TABLE "invTypes" ( > "typeID" integer NOT NULL, > "groupID" integer DEFAULT NULL, > "typeName" varchar(200) DEFAULT NULL, > "description" varchar(6000) DEFAULT NULL, > "mass" double DEFAULT NULL, > "volume" double DEFAULT NULL, > "capacity" double DEFAULT NULL, > "portionSize" integer DEFAULT NULL, > "raceID" integer DEFAULT NULL, > "basePrice" decimal(19,4) DEFAULT NULL, > "published" integer DEFAULT NULL, > "marketGroupID" integer DEFAULT NULL, > "chanceOfDuplicating" double DEFAULT NULL, > PRIMARY KEY ("typeID") > ) > > I should stress that I don't know how the interface got this information > other than that it opened the database file. > > On Tuesday, January 28, 2014 6:36:26 PM UTC-8, Erich Blume wrote: > I am somewhat new to SQLAlchemy, but as far as I can tell there is no > existing facility in the SA SQLite dialect > (sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite) to resolve > SQLite's data type affinities, as shown in section 2.2 of the SQLite data > type docs: http://www.sqlite.org/datatype3.html - and this omission is > causing reflection to fail for me on a specific sqlite database file. > > Here is an example session (apologies if this is poorly formatted, I'm not > used to using the google groups UI for technical discussion): > > >>> import sqlalchemy as sa > >>> eng = sa.create_engine('sqlite:////tmp/eve-asset-db.sqlite') > >>> meta = sa.MetaData() > >>> meta.reflect(bind=eng) > [OMITTED]/python3.3/site-packages/sqlalchemy/dialects/sqlite/base.py:808: > SAWarning: Did not recognize type 'DOUBLE' of column 'sizeFactor' > default, primary_key)) > ... (Many additional similar warnings suppressed) > > By inspecting base.py I see that indeed only the core types are supported, > and 'Affinity Types' like "DOUBLE" and "LONGTEXT" will cause this error. One > question I have that I can't find an answer to is why does this SQLite > database have a column marked as a DOUBLE? Shouldn't it have been converted > to a REAL when it was created? I'm not familiar enough with SQLite to know > the answer to that question. > > Regardless of the answer to that question though, it seems reasonable to me > that the reflection should succeed and automatically convert the columns to > REAL/TEXT/etc. > > Here is a link to a third-party website which has created the sqlite database > I am trying to reflect. Please note that the remote source updates this file > every few months and so viewers from the future might not get the same > behavior: https://www.fuzzwork.co.uk/dump/sqlite-latest.sqlite.bz2 [341MB > uncompressed] > > Here are my environment details: > > Python 3.3 > SQLAlchemy 0.9.1 > > I don't know what version of SQLite was used to create the database. If > there's a way to find out, let me know and I will update. > > Thanks! > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
