Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

I'm a beginner with python/Flask/SQLAlchemy so sorry if my questions are dumb.

I want to create an API with Flask using Flask-SQLAlchemy as following:

  1. one sqlite database for users/passwords

SQLALCHEMY_DATABASE_URI = 'sqlite:////path/to/users.db'

 class User(DB.Model):
    __tablename__ = 'users'
    id = DB.Column(DB.Integer, primary_key=True)
    username = DB.Column(DB.String(64), index=True)
    password = DB.Column(DB.String(128))
  1. Lets say I have multiple "customers" witch a user can create using

    $ http POST http://localhost:5000/api/customers/ name=customer1

    I would have a model like this:

    class CustomerModel(DB.Model):

    __tablename__ = 'customer'
    customer_id = DB.Column(DB.Integer, primary_key=True)
    customer_name = DB.Column(DB.String, unique=True, index=True)
    

I need to create a separate sqlite file (example: customer1.db) for each "customers" so I would have:

SQLALCHEMY_BINDS = {
    'customer1' = 'sqlite:////path/customer1.db',
    'customer2' = 'sqlite:////path/customer2.db',
    ...
}

My questions are:

  1. I do not have fixed number of "customers" so I cannot create a model class for each and specify the "bind_key" for each. Is it possible to do this with Flask-SQLAlchemy or I need to use plain SQLAlchemy?

  2. lets say I have 3 "customers" in data/ as customer1.db, customer2.db and customer3.db. I would start the application and create SQLALCHEMY_BINDS dictionary listing the files in data/ and then DB.create_all().

on a request for a specific "customer" how can I bind to the right .db file using the Flask-SQLAlchemy DB.session? I've seen this but there is no answer and I cannot find the way to do it. Using different binds in the same class in Flask-SQLAlchemy

share|improve this question
up vote 0 down vote accepted

Why exactly do you want entirely separate DB files for each customer?

In any case this is easier with straight SQLAlchemy. You can create a getter function which returns a session pointing to your db file.

def get_session(customer_id):
    sqlite_url = 'sqlite:////path/customer%s.db' % customer_id
    engine = create_engine(sqlite_url)

    # initialize the db if it hasn't yet been initialized
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()

    return session

You can then use and close that session.

But without knowing your specific use case, it is difficult to understand why you would want to do this instead of just using a single SQLite database.

share|improve this answer
    
Hi jumbopap and thanks for the answer. this app will be used in different sites to connect to some servers and get some data. this app will also be used by several persons to look at the data(1 db) and compare with other data (other db). it would be easier just to copy the db file and put it on my db directory and the app would work. I could end up with 100 db files. – theo_ni Mar 3 '16 at 16:13
    
I'm new to SQLAlchemy and do not see exactly the advantage of using Flask-SQLAlchemy but I'm sure I want to use the paginate from it. I'm not sure I can use it with your approach right? – theo_ni Mar 3 '16 at 16:16
    
Everything you are saying can be done easier in a single DB file using relational schema, but your call. Plain SQLAlchemy doesn't come with pagination, but you can easily add the class in from the Flask-SQLAlchemy source. – jumbopap Mar 3 '16 at 16:22
    
ok, thanks. From other questions I found different ways to do it with plain SQLAlchemy but I want to know if it's possible to do it with Flask-SQLAlchemy and binds because I plan to use other packages that integrates with Flask-SQLAlchemy like Flask-Migrate and Flask-Marshmallow. – theo_ni Mar 3 '16 at 16:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.