I have a SQLite database configured with Pyramid Alchemy scaffold in my Linux machine. I have another remote Mysql database which resides in a Windows machine, with loads of data.Now, I have to connect to the remote Mysql database to pull data and populate them into my Sqlite database with the help of sqlAlchemy.
What I had been doing: I used mysql workbench to query data from the mysql database, export results to a csv file, load the csv file into my pyramid initializedb.py through python's default csv module, and then finally insert the retrieved rows into my Sqlite database.
What I want to do: I want to connect to the remote Mysql database from my initializedb.py itself, fetch results and insert them into my sqlite database.
How do I go on about with this? Any help is appreciated.
You can create a connection into your mysql db within the initializedb.py, extract the data and store them into your local db. Now, you will find the basics about sqlalchemy here: http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html
If you need this configurable, then you can put the url of your database into your config and access it through settings.
Also, you do not have to define the structure of your tables exactly, you can use something like:
users = Table('users', metadata, autoload=True)
On the other hand, if your MySQL db structure is the same as the sqlite db structure, it might be possible to reuse your model, but that would probably be hard to explain here without seeing any of your code.