Accessing same db.session across different modules in sqlalchemy

Question

I am very new to sqlalchemy and am trying to figure out how to get things cleaner and connecting.

I have created a /model base.py doc where I have created a session and established all my entities in tables (along with relationships and etc.). I want to create another module in which I operate CRUD operations on the entities (tables) in base.py. This file is called object.py and has the class BaseAPI(object) and has the different functions "create" "read" "update" and "delete". I want to make sure that I am connecting to my table (base.py) in object.py and operating on the entity User. For this case, the entity (table) is Users.

This is what I have in the API object.py doc:

#!/usr/bin/env python

from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from datetime import datetime, timedelta
import notssdb.model
from base import User #importing from the module base.py -- doesn't work

engine = create_engine('sqlite:///./notssdb.db', echo=True) #in-memory sql engine 

# create a Session
Session = sessionmaker(bind=engine)


class BaseAPI(object):
#    DBSession = scoped_session(sessionmaker(engine))
#    users = DBSession.query(User).all()

    def __init__ (self):
       session = Session()


# CREATE USER 

    def create_user(self, username, password, fullname):
        new_user = User(username, password, fullname)
        self.session.commit(new_user)
        print(username, password, fullname)

Am I importing too many things? Do I need to import all the sqlalchemy tools? Does my init constructor under class BaseAPI need to instantiate the DB session?

Answer 1

1. Am I importing too many things? Do I need to import all the sqlalchemy tools?

Sqlalchemy doesn't have it's own coding style, you've to follow Python coding style. If you don't use any module there is no point of importing it.

I don't see this has been used from sqlalchemy.orm import relationship, backref and this should be used while defining models, hence you don't need to import these modules.

2. Does my init constructor under class BaseAPI need to instantiate the DB session?

There is no hard rule that you've initiate session in your BaseAPI, you can even write your programme like this..

#!/usr/bin/env python

from sqlalchemy import create_engine
from sqlalchemy.orm import relationship, backref, sessionmaker
from datetime import datetime, timedelta
import notssdb.model
from base import User #importing from the module base.py -- doesn't work

engine = create_engine('sqlite:///./notssdb.db', echo=True) #in-memory sql engine 

# create a Session
Session = sessionmaker(bind=engine)
session = Session()


class BaseAPI(object):

# CREATE USER 

    def create_user(self, username, password, fullname):
        new_user = User(username, password, fullname)
        session.commit(new_user)
        print(username, password, fullname)

But it's not good practice to club your connection generation part with user manager, I would suggest you follow this way..

Note: This is just a sample code and I didn't execute this, you just have to follow this to structure your code.

First create seperate module for connection management, may be like connection_manager.py with the below content.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///./notssdb.db', echo=True)

# create a Session
Session = sessionmaker(bind=engine)

class SessionManager(object):
    def __init__(self):
        self.session = Session()

And the create your user_manger.py and import your SessionManager here.

from base import User # this is your User model
from connection_manager import SessionManager

class UserManager(SessionManager):

    def create_user(self, username, password, fullname):
        new_user = User(username, password, fullname)
        self.session.commit(new_user)
        print(username, password, fullname)

    def delete_user(self, *args):
        pass # your code

This way you can make your code cleaner.