CODEWITHSUNDEEP
pythonsqlitepython-2.7sqlalchemy
liv2hak
liv2hak

Reputation: 15000

remote database creation in sql alchemy via sqlite

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.

Upvotes: 1

Views: 3281

Answers (2)

peak
peak

Reputation: 116830

Times have changed.

If one wishes to make a SQLite database available over the web, one option would be to use CubeSQL as a server, and SQLiteManager for SQLite as its client. For details, see e.g. https://www.sqlabs.com/

Another option might be to use Valentina Server similarly: see https://www.valentina-db.com/en/valentina-server-overview

(These options will probably only be suitable if there is at most one client with write-access at a time.)

Are there any others?

Upvotes: 0

vvladymyrov
vvladymyrov

Reputation: 5793

Answering your questions:

  1. 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
  2. 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

Upvotes: 2

Related Questions