python - Database table doesn't show up - Stack Overflow

admin管理员组

文章数量:1295783

In the run section I check if table user is created:

Database creation attempted
Existing tables: ['user']
Database URI: sqlite:///database.db

However when I entered sqlite3 database.db .tables nothing showed up. My code:

#Initializing
app = Flask(__name__)
bcrypt = Bcrypt(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'
db = SQLAlchemy(app)
app.config['SECRET_KEY'] = 'secretkey'

#Creating the user class
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), nullable=False, unique=True)
    password = db.Column(db.String(50), nullable=False)

#Running the app
if __name__ == "__main__":
    with app.app_context():
        db.create_all()
        print("Database creation attempted")

    # Check if table exists
        inspector = inspect(db.engine)
        tables = inspector.get_table_names()
        print("Existing tables:", tables)
        print("Database URI:", app.config['SQLALCHEMY_DATABASE_URI'])
    app.run(debug=True)

In the run section I check if table user is created:

Database creation attempted
Existing tables: ['user']
Database URI: sqlite:///database.db

However when I entered sqlite3 database.db .tables nothing showed up. My code:

#Initializing
app = Flask(__name__)
bcrypt = Bcrypt(app)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'
db = SQLAlchemy(app)
app.config['SECRET_KEY'] = 'secretkey'

#Creating the user class
class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), nullable=False, unique=True)
    password = db.Column(db.String(50), nullable=False)

#Running the app
if __name__ == "__main__":
    with app.app_context():
        db.create_all()
        print("Database creation attempted")

    # Check if table exists
        inspector = inspect(db.engine)
        tables = inspector.get_table_names()
        print("Existing tables:", tables)
        print("Database URI:", app.config['SQLALCHEMY_DATABASE_URI'])
    app.run(debug=True)

• python
• sqlite
• flask
• sqlalchemy

Share Improve this question Follow edited Jan 26 at 23:53 user4157124 2,9041414 gold badges3030 silver badges4444 bronze badges asked Jan 23 at 13:33 ijuncodesijuncodes 111 bronze badge 3

• Try sqlite3 instance/database.db .tables – pybynumbers Commented Jan 23 at 14:01
• It worked! But I'm curious why I need to add instance. – ijuncodes Commented Jan 23 at 14:09
• it's just where sqlite database.db is creatied by default, in the /instance subfolder not in the project root – pybynumbers Commented Jan 23 at 14:12

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Solution 1: Ensure Proper File Path Check the path of the database.db file to confirm it's being created in the expected location. SQLite might be creating the file in a different directory. Add this line to verify the absolute path:

python

import os
print("Database file location:", os.path.abspath("database.db"))

Run this and confirm the file exists at the printed location.

Solution 2: Commit the Changes Make sure changes are committed to the database. SQLAlchemy works with transactions, and even though db.create_all() doesn't explicitly require a commit, there can be rare scenarios where ensuring a flush or commit resolves issues.

Modify your code to explicitly commit the session after creating tables:

python

if __name__ == "__main__":
with app.app_context():
    db.create_all()
    db.session.commit()  # Ensure all changes are committed
    print("Database creation attempted")

# Check if table exists
    inspector = inspect(db.engine)
    tables = inspector.get_table_names()
    print("Existing tables:", tables)
    print("Database URI:", app.config['SQLALCHEMY_DATABASE_URI'])
app.run(debug=True)

Solution 3: Check SQLite Database Version Ensure that you're using the same SQLite version as the one bundled with SQLAlchemy. Sometimes there are compatibility issues. Run the following command in your Python script to print the SQLite version:

python

import sqlite3
print("SQLite version:", sqlite3.sqlite_version)

Compare this version with the SQLite version you're using from the command line (sqlite3).

Solution 4: Verify Permissions Check if the database.db file is writable and accessible. If the process doesn't have permission to write to the directory, the table won't be persisted. Run this:

python

import os
print("Writable:", os.access(os.path.abspath("database.db"), os.W_OK))

If it's not writable, check your file and directory permissions.

Solution 5: Debug with SQLAlchemy Echo Enable SQLAlchemy's echo mode to see the actual SQL queries being executed. This can help you verify that the CREATE TABLE command is executed:

python

app.config['SQLALCHEMY_ECHO'] = True

When you run the app, you'll see all SQL statements in the console.

Solution 6: Use sqlite3 Correctly Finally, after ensuring that the database file exists and contains the table, run sqlite3 in the correct directory. For example:

bash

sqlite3 /full/path/to/database.db

Then, use .tables to confirm the table exists.

本文标签： pythonDatabase table doesn39t show upStack Overflow