Migrate Sqlalchemy schema from mssql to sqlite db?

Question

I have all my table classes written for mssql but now I want to test my application locally so I need sqlitedb.Is there a way through which I can Replicate my database in sqlite. I am facing some issues like sqlite does not support Float as a Primary key.I have more than 200 tables I can not go and edit all just for testing.I can have all the tables in one metadata. My idea is to use sqlite just for testing and for production I will still be using mssql. Note I changed Float to Integer but still my tables are not created instead it just creates a empty db. My code for table in metadata.tables:

keys_to_change = []
for pkey_column in metadata.tables[table].primary_key.columns.keys():
            keys_to_change.append(pkey_column)

for data in list(metadata.tables[table].foreign_keys):
            keys_to_change.append(data.column.name)

for column in metadata.tables[table].columns:
            if column.name in keys_to_change:

                if str(column.type) == 'FLOAT':            
                    column.type = INTEGER
engine = create_engine('sqlite:///mytest.db', echo=True, echo_pool=True)
metadata.create_all(engine)

It's not a good idea to test code using different DB system than the one used in production, the feature disparity will most likely be too great. I'd suggest finding a way to test using MS SQL Server. — Audrius Kažukauskas, Jul 03 '14 at 14:08
Testing guys are testing it with mssql but in my end I just wanna replicate the db and perform some functional tests. — Anurag, Jul 03 '14 at 15:37

van · Answer 1 · 2014-07-03T16:35:24.170

1

If you are able to change the model code, I would suggest to create an alias to the Float and use it to define those primary_key and ForeignKey columns, which you could just change for your sqlite testing:

# CONFIGURATION
PKType = Float # default: MSSQL; or Float(N, M)
# PKType = Integer # uncomment this for sqlite

and your model becomes like below:

class MyParent(Base):
    __tablename__ = 'my_parent'
    id = Column(PKType, primary_key=True)
    name = Column(String)
    children = relationship('MyChild', backref='parent')

class MyChild(Base):
    __tablename__ = 'my_child'
    id = Column(PKType, primary_key=True)
    parent_id = Column(PKType, ForeignKey('my_parent.id'))
    name = Column(String)

Alternatively, if you would like to be only changing the engine and not another configuration variable, you can use dialect-specific custom type handling:

import sqlalchemy.types as types
class PKType(types.TypeDecorator):
    impl = Float
    def load_dialect_impl(self, dialect):
        if dialect.name == 'sqlite':
            return dialect.type_descriptor(Integer())
        else:
            return dialect.type_descriptor(Float())

edited Jul 03 '14 at 16:35

answered Jul 03 '14 at 15:45

van

74,297
13
168
171

Thanks I resolved this but now I have to fix circular dependency. – Anurag Jul 04 '14 at 05:39
My problem was solved with the use of use_alter=True. My table structure were a little complicated and There were too many cyclic refrences which got resolved by using use_alter = True for foregin key constraints. – Anurag Jul 04 '14 at 08:01
Found this in sql alchemy website it says why cyclic dependency exception occurs and its solution just applied it in my code. http://docs.sqlalchemy.org/en/rel_0_7/core/exceptions.html – Anurag Jul 04 '14 at 10:57
No, I meant: which way did you go regarding the original question? – van Jul 04 '14 at 11:50
ohh k I looped through tables in metadata and changed all primary keys type to Integer. – Anurag Jul 04 '14 at 11:52

Migrate Sqlalchemy schema from mssql to sqlite db?

1 Answers1