Python Forum

I'm trying to understand how to use SQLAlchemy. I've written a piece of code that should simply add one record to an existing database. When I run the code I don't get an error, but nothing writes to the database. I'm obviously doing something wrong, but I can't spot it.

I'm using Python 3.7.11. SQLAlchemy is 2.0.12.

#!/cmts/fastapi/sv/venv/bin/python3

from datetime import datetime

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, Integer, String, Date

# Location of the test catalog database
SQLALCHEMY_DATABASE_URL = "sqlite:///./test_catalog.db"

engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})

Base = declarative_base()

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


class Test(Base):
    __tablename__ = "catalog"

    id = Column(String, primary_key=True)  # Identifying ID for this test
    parent_id = Column(String)  # ID of test that this was generated from, if not a parent test
    request_id = Column(String)  # UUID ID used to group multiple tests that are submitted at the same time
    test_type = Column(String)  # Type of test: Online, Impaired, Network, etc...
    target = Column(String)  # Target for this test
    user = Column(String)  # Username of who initiated the test
    submitted = Column(Date)  # When was this test submitted
    started = Column(Date)  # When was this test started
    ended = Column(Date)  # When was this test completed
    progress = Column(Integer, default=0)  # Progress indicator for this test, from 0 to 100
    state = Column(String, default="New")  # State of the test: New, Running, Done, Error
    result = Column(String)  # JSON results of the test. Compressed?


def get_db():
    try:
        db: Session = SessionLocal()
        return db
    except Exception as e:
        print(e)
#    finally:
#        db.close()


Base.metadata.create_all(bind=engine)

db = get_db()

test_model = Test()
test_model.id = "1234567890"
test_model.parent_id = None
test_model.request_id = "0987654321"
test_model.target = "target"
test_model.user = "user"
test_model.test_type = "test"
test_model.submitted = datetime.now()
test_model.started = None
test_model.ended = None
test_model.progress = 0
test_model.state = "New"
test_model.result = None

db.add(test_model)
db.commit()

print("Added")

print(db.query(Test).all())

db.close()

I check my database, and there are no rows:

$ sqlite3 test_catalog.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from catalog;
sqlite>

If you want to use the DB, why are you closing it in get_db? What's the purpose of the db.close() there?

If you want to close it on an exception, put that in the exception block, not a finally block.

(Jun-01-2023, 03:23 PM)bowlofred Wrote: [ -> ]If you want to use the DB, why are you closing it in get_db? What's the purpose of the db.close() there?

If you want to close it on an exception, put that in the exception block, not a finally block.

When I get a successful connection, it will return from the function, so never do the finally block. The db shouldn't close.

FYI: SQLalchemy tutorial on this forum.

(Jun-01-2023, 05:12 PM)Larz60+ Wrote: [ -> ]FYI: SQLalchemy tutorial on this forum.

Thanks!

I'm looking it over but I don't understand any of it.

I'm learning SQLAlchemy because I need to create/access a database with a FastAPI API, which I'm also learning.

from command line. please start sqlite3
on command line, type schema catalog
check all column names and lengths, are they correct?

(Jun-02-2023, 11:06 AM)Larz60+ Wrote: [ -> ]from command line. please start sqlite3
on command line, type schema catalog
check all column names and lengths, are they correct?

Is this what you mean?

sqlite> sqlite> .schema catalog
CREATE TABLE catalog (
        id VARCHAR NOT NULL,
        parent_id VARCHAR,
        request_id VARCHAR,
        test_type VARCHAR,
        target VARCHAR,
        user VARCHAR,
        submitted DATE,
        started DATE,
        ended DATE,
        progress INTEGER,
        state VARCHAR,
        result VARCHAR,
        PRIMARY KEY (id)
);
sqlite>

The table looks good to me.

sqlite> pragma table_info(catalog);
0|id|VARCHAR|1||1
1|parent_id|VARCHAR|0||0
2|request_id|VARCHAR|0||0
3|test_type|VARCHAR|0||0
4|target|VARCHAR|0||0
5|user|VARCHAR|0||0
6|submitted|DATE|0||0
7|started|DATE|0||0
8|ended|DATE|0||0
9|progress|INTEGER|0||0
10|state|VARCHAR|0||0
11|result|VARCHAR|0||0
sqlite>

So the table is being created properly, just not loaded.
I'll get back.

So, I found one error that I made, but it still does not resolve the issue...

I was using commit instead of commit() .

I am fixing the sample above now.

What commands should I be using to query the all rows of the table "catalog"?

I want to stop having to jump between my script and the SQLite prompt to check if the database has been updated.