Setting up MySQL / access with python on Mac OS09 Sep 2017
Today I wanted to setup an automated kickstarter scraper on Python Anywhere but realized that only MySQL is freely supported there (while I’ve been using PostgreSQL). So, a time to switch?
Here is how I install MySQL on my Mac and have it accessed with SQLAlchemy:
- Download and install MySQL from Oracle.
- Go to System Preferences to start the MySQL server.
- Navigate to the bin directory and login with the temporary password shown at the end of the installation:
cd /usr/local/mysql/bin ./mysql -u root -p
- Create another set of username and password that you use instead of root.
CREATE USER username@localhost IDENTIFIED BY 'password'
- I have installed pymysql and sqlalchemy in Python to access the MySQL database. To access the database:
from sqlalchemy import create_engine from sqlalchemy_utils import database_exists, create_database # dbname is the database name # user_id and user_password are what you put in above engine = create_engine("mysql+pymysql://%s:%s@localhost:3306/%s" %(user_id,user_password,dbname),echo=False) if not database_exists(engine.url): create_database(engine.url) # Create database if it doesn't exist. con = engine.connect() # Connect to the MySQL engine table_name = 'new_table' command = "DROP TABLE IF EXISTS new_table;" # Drop if such table exist con.execute(command)
Executing SQL commands is rather easy by using: