remote database creation in sql alchemy via sqlite

Question

I have an sqlalchemy application that currently uses a local database.The code for the application is given below.

log = core.getLogger()

engine = create_engine('sqlite:///nwtopology.db', echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
session = Session()

class SourcetoPort(Base):
    """"""
    __tablename__ = 'source_to_port'
    id = Column(Integer, primary_key=True)
    port_no        = Column(Integer)
    src_address    = Column(String,index=True)

    #-----------------------------------------
    def __init__(self, src_address,port_no):
        """"""
        self.src_address = src_address
        self.port_no     = port_no

I want to create the database itself in a remote machine.I came across this document. http://www.sqlalchemy.org/doc_pdfs/sqlalchemy_0_6_3.pdf

In the explanation they mentioned the lines given below.

engine = create_engine(’postgresql://scott:tiger@localhost:5432/mydatabase’)

My first question is

1) does sqlite support remote database creation? 2) How do I keep the connection to the remote machine open always? I don't want to initiate an ssh connection every time I have to insert an entry or make a query.

These question may sound stupid but I am very new to python and sqlalchemy.Any help is appreciated.

vvladymyrov vvladymyrov · Accepted Answer · 2013-05-08T14:46:07

Answering your questions:

SQLite doesn't support remote database connection - you'll have to implement this by yourself - like putting sqlite database file on shared by network filesystem, but it would make your solution less reliable
My suggestion - do not try to use user remote sqlite database but switch to traditional RDBMS. Please see below for more details.

Sounds like your application had overgrown SQLite. And it is good time to switch to using traditional RDBMS like MySQL or PosgreSQL where network connections are supporting out of the box.

SQLite is local database. SQLite has a page explaining when to use it. It says:

If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite.

The good thing is that your application might be database agnostic as you are using SQLAlchemy for generating queries.

So I would do the following:

install database system to machine (it doesn't matter - local or remote, you can always repeat move your database to remote machine) and configure permissions for your user (create database, alter, select, update and insert)
create database schema and populate data - to clone your existing. There are some tools available for doing so - i.e. Copying Databases across Platforms with SQLAlchemy sqlite database.
update db connection string in your application from using sqlite to use remote database of your choice

remote database creation in sql alchemy via sqlite

2 Answers