Section 2.1 ( http://www.sqlite.org/datatype3.html ) has a deterministic algorithm for transforming an unknown column type in to it's inverse-affinity real type. It seems to me that we could modify the code in base.py around 820 (I'm at home and working from memory) to implement that algorithm if the chosen schema is sqlite and if the column type is not found in ischema_names. I'd be happy to cook up a patch for that if it's not too objectionable to anyone.
I'll try and see if I can find out how the author created this database with the incorrect column type names. If s/he used an official binary to create it then I feel it's probably important for SQLAlchemy to support it. Thanks for the workaround in the mean time! On Tuesday, January 28, 2014 7:53:02 PM UTC-8, Michael Bayer wrote: > > 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] <javascript:>> > 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] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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.
