How to connect to a database using a function and sqlalchemy

Question

I am trying to create a function to connect to a database via sqlalchemy but I can't get it to work. It works if I keep it outside of a function but not when it's within.

I've tried using global variables but that doesn't seem to make any difference

dbserver = "@123.00.00.00/"
dbname = "database"

username = input("Username: ")
userpass = getpass.getpass("Password: ")

def db_connect():
    db = create_engine('mysql+pymysql://'+username+':'+userpass+dbserver+dbname, 
    pool_recycle=3600, echo=False)
    con = engine.connect()

If I try to connect to the database via this method and then attempt to edit anything within it, I get an error saying "this connection is closed".

Hi @scottapotamus, do you mind sharing what you try to do? Is it inside the function, outside, after con , etc? — rrcal
– rrcal, Commented Jul 31, 2019 at 1:29
so if I try to update information in the database via SQL, it tells me the connection is closed. I run this code from within another function. — scottapotamus
– scottapotamus, Commented Jul 31, 2019 at 1:38

O.O · Accepted Answer · 2019-08-03 19:09:55Z

1

Your question is missing some parts, that's why I can't figure out exactly what is the problem.

But below I wrote a simple sample that should help you :

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)


def db_connect():
    basedir = os.path.abspath(os.path.dirname(__file__))
    SQLALCHEMY_DATABASE_URI = 'sqlite:///'+os.path.join(basedir,'dbname')
    engine = create_engine(SQLALCHEMY_DATABASE_URI)
    Base.metadata.create_all(engine)

    conn = engine.connect()
    d = insert(Department).values(name="IT2")    
    conn.execute(d)
    return conn


conn = db_connect()
result = conn.execute('select * from department').fetchall()
print(result

Result is : [(1, 'IT'), (2, 'IT'), (3, 'IT'), (4, 'IT'), (5, 'IT1'), (6, 'IT1'), (7, 'IT1'), (8, 'IT2')]

I hope it will help!

answered Aug 3, 2019 at 19:09

O.O

1,30811 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

scottapotamus Over a year ago

Thanks, it was the return statement I was missing in the end! Not sure how I managed to forget that.

Collectives™ on Stack Overflow

How to connect to a database using a function and sqlalchemy

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related