Forums

Hello pythonites:

I am having a problem with pythonanywhere with sqlite3 databases. I have a FLASK app that I have set as a web app on PA. I have read the other posts about databases and relative/absolute paths, etc. In my code, the database is created, but the tables are never created. It is wierd. I get a 0 byte file (account_2026.db). When I upload my version of the database, sqlite3 on PA reads and writes the database fine. But if the database does not exist, the first conn creates a 0 byte database, but it does not create any of the tables. Also, where do the output log entries go? This code runs fine in visual studio code, but on PA, I can't see the good messages or the error messages. I see some messages in the error logs, but they are not related to any of these log entries in the code. Where can i find the messages in PA? I am logging on success as well as failure, but I can't see any of the messages anywhere. Pls help!

#Database path
ACCOUNT = '/home/gopalnn/account_2026.db'
def init_db():
try:
    # Attempt to connect to the local SQLite database

   with sqlite3.connect(ACCOUNT) as conn:
        cursor = conn.cursor()

        # Create the account table if it does not exist
        try:
            cursor.execute('''CREATE TABLE IF NOT EXISTS account (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                userid TEXT,
                password TEXT,
                guid TEXT,
                timestamp TEXT
            )''')
            conn.commit()
            logging.info("Account table created or verified successfully.")
        except sqlite3.Error as e:
            logging.error(f"Error creating 'account' table: {e}")

        # Create the inventory table if it does not exist
        try:
            cursor.execute('''CREATE TABLE IF NOT EXISTS inventory (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                guid TEXT,
                originalPrompt TEXT,
                category TEXT,
                item TEXT,
                count TEXT,
                timestamp TEXT
            )''')
            conn.commit()
            logging.info("Inventory table created or verified successfully.")
        except sqlite3.Error as e:
            logging.error(f"Error creating 'inventory' table: {e}")

        # Create the preference table if it does not exist
        try:
            cursor.execute('''CREATE TABLE IF NOT EXISTS preference (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                guid TEXT,
                originalPrompt TEXT,
                category TEXT,
                type TEXT,
                preference TEXT,
                timestamp TEXT
            )''')
            conn.commit()
            logging.info("Preference table created or verified successfully.")
        except sqlite3.Error as e:
            logging.error(f"Error creating 'preference' table: {e}")

except sqlite3.Error as e:
    logging.error(f"Database connection error: {e}")

Link to logs are available on the "Web" page on PythonAnywhere. What errors do you see there?

Hi I have looked at all three log files link from the "web" tab of PA and I do not see any messages that relate to any database activity. Where do the "print()" statements output to? I would have expected to see either the "table created" message or the "error" message, but I see none of them. Is there a "delay" in refreshing the log files? strangely, the code works fine if the database already exists, but if i create a new one, the tables never get created.

Actually, I think I should clarify. When I run this webapp the Init_db is getting called. If there is no "account_2026.db" file at the path, PA creates a new 0 byte database, but no tables are created. If I upload my copy of a database created (with tables) from my local setup tp PA, everything works fine. It's the creation of the initial "account_2026.db" database tables that are the problem. And I can't see any log messages related to the attempt to create the tables in the 0 byte database. So Any other query against that 0 byte database (obviously) says 'Error - no such table ' - accounts, inventory, or preference depending on what i am selecting. This code in init_db is supposed to create the db, and the associated tables. Thanks.

Also, just as another data point, if I run this code in a console, it creates the database and the tables just fine. However, If I run it as a web app under PA, the database gets created with 0 bytes. but no tables are created.

Exactly where those log outputs would go would depend on how you've configured the logging module. Just to simplify things, if you replace the various logging.info and logging.error calls with prints, and add the flush=True kwarg to the end -- for example:

print(f"Database connection error: {e}", flush=True)

...then we can be certain that the output will go to your website's server log, which hopefully will help you track things down.

BTW just for clarity: the creation of the zero-byte database file is done by SQLite, not by anything on our side.