DEV Community: Misbah bagaskara purwanto

SQLAlchemy Foreign Key Error: "Could not find table 'user' for announcement.creator_id"

Misbah bagaskara purwanto — Tue, 26 Nov 2024 13:49:57 +0000

Problem Description:

I'm encountering an error when running my Flask application. The error occurs when I try to log in, and it seems related to the Announcement model's foreign key referencing the User model. Here's the error traceback:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'announcement.creator_id' could not find table 'user' with which to generate a foreign key to target column 'id'

Relevant Code:

Here are the models involved:

User Model:

class User(db.Model, UserMixin):
    __bind_key__ = 'main'  # Bind to 'main' database
    __tablename__ = 'user'
    metadata = metadata_main  # Explicit metadata
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password_hash = db.Column(db.String(128), nullable=False)
    role = db.Column(db.String(20), nullable=False)
    is_admin_field = db.Column(db.Boolean, default=False)

    def set_password(self, password):
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    @property
    def is_admin(self):
        """Return True if the user is an admin."""
        return self.role == 'admin'

    def get_role(self):
        """Return the role of the user."""
        return self.role

    def __repr__(self):
        return f"User('{self.username}', '{self.email}', '{self.role}')"

Announcement Model:

class Announcement(db.Model):
    __bind_key__ = 'main'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(150), nullable=False)
    content = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    created_by = db.Column(db.String(50), nullable=False)

    # ForeignKeyConstraint ensures the reference to user.id in 'main' database
    creator_id = db.Column(db.Integer, nullable=False)
    __table_args__ = (
        ForeignKeyConstraint(
            ['creator_id'],
            ['user.id'],
            name='fk_creator_user_id',
            ondelete='CASCADE'
        ),
    )

    def __repr__(self):
        return f"<Announcement {self.title}>"

Where the Module Was Declared:

# school_hub/__init__.py
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_login import LoginManager
from flask_migrate import Migrate

# Initialize extensions
db = SQLAlchemy()
login_manager = LoginManager()
migrate = Migrate()

def create_app():
    app = Flask(__name__)

    # Configurations
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:Root1234!@localhost/school_hub'
    app.config['SECRET_KEY'] = '8d8a72493996de3050b75e0737fecacf'
    app.config['SQLALCHEMY_BINDS'] = {
        'main': 'mysql+pymysql://root:Root1234!@localhost/main_db',
        'teacher_db': 'mysql+pymysql://root:Root1234!@localhost/teacher_database',
        'student_db': 'mysql+pymysql://root:Root1234!@localhost/student_database',
    }
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    # Initialize extensions with the app
    db.init_app(app)
    login_manager.init_app(app)
    migrate.init_app(app, db)

    # Set up Flask-Login user loader
    from .models import User  # Import User model here to ensure it's loaded

    @login_manager.user_loader
    def load_user(user_id):
        return User.query.get(int(user_id))

    # Register Blueprint
    from .routes import main
    app.register_blueprint(main)

    # Ensure app context is pushed before calling db.create_all()
    with app.app_context():
        # Create all tables for the 'main' database
        db.create_all()  # This will create tables for the default 'main' database

        # Explicitly create tables for the 'teacher_db' and 'student_db'
        from .models import Teacher, Student, User  # Ensure models are imported

        # Create tables for 'teacher_db'
        Teacher.metadata.create_all(bind=db.get_engine(app, bind='teacher_db'))

        # Create tables for 'student_db'
        Student.metadata.create_all(bind=db.get_engine(app, bind='student_db'))

    return app

My Environment:

Flask: Latest version
Flask-SQLAlchemy: Latest version
SQLAlchemy: Latest version
Python: Latest version

My Question:

Why is SQLAlchemy unable to find the user table, even though the table name matches the foreign key reference? How can I resolve this error?

Additional Context:

I'm using Flask-Migrate for database migrations. The User model is bound to the main database, and the Announcement model references this table. The error occurs when SQLAlchemy tries to create the foreign key constraint, and it cannot find the user table.

What Did I Try?

Ensuring Correct Database Binding:
- I’ve ensured both models explicitly set __bind_key__ = 'main' to associate them with the same database.
Ensuring Correct Foreign Key Reference:
- The Announcement model has a foreign key referencing the id column of the User model:
```
 creator_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
```

I verified that the User model is correctly bound to 'main' and the user table exists.

Database Initialization:
- I’ve ensured that tables are created in the correct order, with the User table being created before the Announcement table due to the foreign key constraint.
Error Handling and Suggestions:
- I’ve checked that both the User and Announcement models are correctly imported and initialized.
- I’ve confirmed that the foreign key reference should work as both models are bound to the same database.
Repeated Checks on Database Bind:
- I double-checked the bind for the User and Announcement models, ensuring both are using 'main' as the bind key.
Potential Missing Table Issue:
- The error could happen if the User table isn’t visible to SQLAlchemy at the time of the foreign key creation, so I ensured that the User table exists and is properly created before running the Announcement model migration.

Struggling with Image Display and Undefined Variables in PHP Script — Need Help!

Misbah bagaskara purwanto — Sat, 27 Jul 2024 03:44:20 +0000

Hey, dev.to community,

I’m working on a PHP-based online shop and encountering a couple of issues in my onlineshop.php script. I could use some guidance on how to resolve these problems. Here’s a summary of the issues I’m facing:

Context:

File Path: /var/www/html/online_shop/public/onlineshop/onlineshop.php
Issue: Images aren’t displaying correctly, and I’m receiving PHP warnings about undefined variables.

Code Snippet:

<?php
session_start();
include_once 'db_connect.php';

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Handle logout if logout parameter is set
if (isset($_GET['logout']) && $_GET['logout'] == 'true') {
    $_SESSION = array();
    session_destroy();
    header("Location: onlineshop.php");
    exit();
}

// Check if the user is logged in
if (isset($_SESSION['user_id'])) {
    $navbar_links = '
        <li class="nav-item">
            <a class="nav-link" href="profile.php">Profile</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" href="onlineshop.php?logout=true">Logout</a>
        </li>
    ';
} else {
    $navbar_links = '
        <li class="nav-item">
            <a class="nav-link" href="login.php">Login</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" href="register.php">Register</a>
        </li>
    ';
}

// Fetch products from database
$sql_fetch_products = "SELECT * FROM products";
$result = $conn->query($sql_fetch_products);

if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

$user_id = $_SESSION['user_id'];

// Fetch username based on user_id
$sql = $conn->prepare("SELECT username FROM users WHERE id = ?");
$sql->bind_param('i', $user_id);
$sql->execute();
$sql->bind_result($username);
$sql->fetch();
$sql->close();

// Fetch chat messages
$chat_query = "SELECT * FROM chat_messages WHERE sender='$username' OR receiver='$username' ORDER BY timestamp DESC";
$chat_result = $conn->query($chat_query);

// Handle sending chat messages
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['chat_message'])) {
    $receiver = 'admin'; // Static value for admin
    $message = $_POST['chat_message'];
    $sender = $username;

    $stmt = $conn->prepare("INSERT INTO chat_messages (sender, receiver, message) VALUES (?, ?, ?)");
    $stmt->bind_param("sss", $sender, $receiver, $message);
    $stmt->execute();
    $stmt->close();
    header("Location: onlineshop.php");
    exit;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Online Shop</title>
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
</head>
<body>
    <!-- Navbar -->
    <nav class="navbar navbar-expand-lg navbar-light bg-light">
        <a class="navbar-brand" href="#">Indonesian Product</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
            <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
            <ul class="navbar-nav ml-auto">
                <li class="nav-item">
                    <a class="nav-link" href="onlineshop.php">Home</a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="about.php">About</a>
                </li>
                <li class="nav-item">
                    <a class="nav-link" href="contact.php">Contact</a>
                </li>
                <?php echo $navbar_links; ?>
            </ul>
        </div>
    </nav>

    <div class="container">
        <div class="marquee">
            <marquee behavior="scroll" direction="left">Welcome to our Online Shop!</marquee>
        </div>
    </div>

    <div class="container">
        <h1 class="mt-4 mb-4">Products</h1>
        <div class="card-columns">
            <?php
            if ($result && $result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    echo '<div class="card">';
                    echo '<a href="product_detail.php?product_id=' . $row['id'] . '">';

                    // Image path
                    $imagePath = '/online_shop/public/' . $row['image'];
                    echo 'Image Path: ' . $imagePath . '<br>';

                    // Verify if the file exists
                    if (file_exists('/var/www/html' . $imagePath)) {
                        echo 'File exists<br>';
                    } else {
                        echo 'File does not exist<br>';
                    }

                    // Display the image
                    echo '<img src="' . $imagePath . '" class="card-img-top" alt="Product Image">';
                    echo '<div class="card-body">';
                    echo '<h5 class="card-title">' . $row['name'] . '</h5>';
                    echo '<p class="card-text">' . $row['description'] . '</p>';
                    echo '<p class="card-text">Price: ' . $row['price'] . '</p>';
                    echo '</div>';
                    echo '</a>';
                    echo '</div>';
                }
            } else {
                echo '<p>No products found.</p>';
            }
            ?>
        </div>
    </div>

   <!-- Chat Section -->
   <div class="card">
        <div class="card-header">Chat with Admin</div>
        <div class="card-body">
            <div class="chat-box" style="height: 300px; overflow-y: scroll;">
                <?php while ($chat = $chat_result->fetch_assoc()) { ?>
                    <div>
                        <strong><?php echo htmlspecialchars($chat['sender']); ?>:</strong>
                        <span><?php echo htmlspecialchars($chat['message']); ?></span>
                        <small class="text-muted"><?php echo $chat['timestamp']; ?></small>
                    </div>
                <?php } ?>
            </div>
            <form action="onlineshop.php" method="POST">
                <div class="form-group">
                    <label for="chat_message">Message</label>
                    <textarea class="form-control" id="chat_message" name="chat_message" required></textarea>
                </div>
                <button type="submit" class="btn btn-primary">Send</button>
            </form>
        </div>
    </div>

    <script>
        $(document).ready(function() {
            function checkNewMessages() {
                $.ajax({
                    url: 'check_new_messages.php',
                    method: 'GET',
                    success: function(data) {
                        if (data.new_messages > 0) {
                            $('#chat-notification-count').text(data.new_messages);
                        } else {
                            $('#chat-notification-count').text('');
                        }
                    }
                });
            }

            // Check for new messages every 5 seconds
            setInterval(checkNewMessages, 5000);
        });
    </script>

    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Issues:

Image Path Problem: The image path generated for displaying product images is incorrect. The URL is showing as /photo_product/photo_product/Screenshot%202024-07-01%20080501.png, which leads to a 404 Not Found error.
PHP Warnings: I’m seeing warnings related to undefined variables and array offsets.

Questions:

Image Path: How can I correct the image path to ensure that images are displayed properly?
PHP Warnings: What steps can I take to resolve the warnings related to undefined variables and array offsets?
Debugging Advice: Any suggestions on effective debugging techniques for these issues?

Any help or insights would be greatly appreciated. Thanks in advance!