Setting up MySQL / access with python on Mac OS
09 Sep 2017Today 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:
con.execute(command)
Enjoy! :)