Day 1 With MySQL - Setting the DB up on PythonAnywhere cloud and running some tests

To See All Articles About Technology: Index of Lessons in Technology

How do I create a MySQL database in PythonAnywhere?

How to Create a MySQL Database in PythonAnywhere

PythonAnywhere provides a MySQL database for its users, but you need to set it up first. Follow these steps:

---

🔹 Step 1: Log in to PythonAnywhere

1. Go to PythonAnywhere and log in.
2. Navigate to the Databases tab.

---

🔹 Step 2: Create a New MySQL Database

1. Click "Add a new MySQL database".
2. Choose a database name (e.g., mydatabase).
3. Click Create.
4. PythonAnywhere will generate:
   • A database name (e.g., your_username$mydatabase).
   • A MySQL username (same as your PythonAnywhere username).
   • A MySQL password (auto-generated, but you can reset it).
   • A MySQL host (usually your_username.mysql.pythonanywhere-services.com).

---

🔹 Step 3: Connect to MySQL from PythonAnywhere Console

To check if the database is working, open a Bash console in PythonAnywhere and run:

bash
mysql -u your_username -h your_username.mysql.pythonanywhere-services.com -p

Enter your MySQL password when prompted.

To list databases:

sql
SHOW DATABASES;

To switch to your database:

sql
USE your_username$mydatabase;

---

🔹 Step 4: Connect to MySQL from a Flask App

Install mysql-connector-python (if not already installed):

bash
pip install mysql-connector-python

Then, create a Python script (app.py):

python
import mysql.connector

# Database credentials from PythonAnywhere
DB_CONFIG = {
    "host": "your_username.mysql.pythonanywhere-services.com",
    "user": "your_username",
    "password": "your_mysql_password",
    "database": "your_username$mydatabase"
}

# Connect to MySQL
try:
    db = mysql.connector.connect(**DB_CONFIG)
    cursor = db.cursor()
    print("Connected to MySQL successfully!")

    # Create a table (if needed)
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100),
            email VARCHAR(100) UNIQUE
        )
    """)
    print("Table created!")

    # Close connection
    cursor.close()
    db.close()

except mysql.connector.Error as err:
    print(f"Error: {err}")

Run the script:

bash
python app.py

---

🔹 Step 5: Access MySQL from PythonAnywhere Web App

If you're deploying a Flask app on PythonAnywhere:

1. Go to "Web" and create a new Flask app.
2. Make sure app.py uses the correct MySQL hostname (not localhost but your_username.mysql.pythonanywhere-services.com).
3. Restart the web app after changes.

---

✅ Final Checklist

✔ Use your full database name (your_username$mydatabase).
✔ Use PythonAnywhere’s MySQL hostname (your_username.mysql.pythonanywhere-services.com).
✔ Avoid using localhost in your connection string.

Let me know if you need further help! 🚀

Testing the MySQL Database

mysql> use ashishjaindrive1$default
Database changed
mysql> 
mysql> create table users (name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into users values('ram');
Query OK, 1 row affected (0.01 sec)

mysql> insert into users values('ravan');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> 



How to install python-dotenv?  

12:23 ~/mysite $ pip install python-dotenv
Defaulting to user installation because normal site-packages is not writeable
Looking in links: /usr/share/pip-wheels
Collecting python-dotenv
    Downloading python_dotenv-1.0.1-py3-none-any.whl (19 kB)
Installing collected packages: python-dotenv
Successfully installed python-dotenv-1.0.1
12:25 ~/mysite $