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
– Audrius Kažukauskas, Commented Jul 3, 2014 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
– Anurag, Commented Jul 3, 2014 at 15:37

van · Accepted Answer · 2014-07-03 16:35:24Z

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 3, 2014 at 16:35

answered Jul 3, 2014 at 15:45

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anurag Over a year ago

Thanks I resolved this but now I have to fix circular dependency.

Anurag Over a year ago

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 Over a year ago

Found this in sql alchemy website it says why cyclic dependency exception occurs and its solution just applied it in my code. docs.sqlalchemy.org/en/rel_0_7/core/exceptions.html

van Over a year ago

No, I meant: which way did you go regarding the original question?

Anurag Over a year ago

ohh k I looped through tables in metadata and changed all primary keys type to Integer.

Collectives™ on Stack Overflow

Migrate Sqlalchemy schema from mssql to sqlite db?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related