How to execute an sqlalchemy script with mysql to create a database

Question

I am very new to Flask and SQLAlchemy. After looking at howto create db mysql with sqlalchemy I have made a simple file in SQLAlchemy that connects to the MySQL database then executes CREATE DATABASE IF NOT EXISTS dbname. I am just wondering how to run this in MySQL in order to test this out. Everywhere I looked seemed to mention how to write things in SQLAlchemy but I am confused about how to execute it. Sorry for the silly question.

Do you want to run the db creation command at the MySQL console? If so, this isn't a SQLAlchemy question, just MySQL: Launch the MySQL console with `mysql -u user -p`, enter the password, then type `CREATE DATABASE IF NOT EXISTS dbname;` and hit enter. Check your work by typing `SHOW DATABASES;` and seeing if `dbname` is listed there. If on the other hand you just want to run the code you've written, if it's all in the global scope (not wrapped in functions), go to your command line and type `python my_script.py`. — Zev Averbach, Jun 10 '17 at 02:49

score 2 · Answer 1 · answered Jun 11 '17 at 15:19

You actually seem to be confused, as even the title mixing up all the things - "How to execute an sqlalchemy script with mysql to create a database".

I am not really sure what you mean by "sqlalchemy script" (assuming that you mean python script which is using the sqlalchemy library), also we don't execute sqlalchemy code with mysql - we write the code (we can call it script) in python using the sqlalchemy library that creates SQL queries based on our code, it connects to the mysql database server, sends SQL queries to it, receives results and returns back to your code.

The CREATE DATABASE IF NOT EXISTS dbname is an SQL instruction to create the database, you can execute it either from python code (see, for example, this reply to the question you linked to - https://stackoverflow.com/a/10770526/4612064):

import sqlalchemy
engine = sqlalchemy.create_engine('mysql://user:password@server') # connect to server
engine.execute("CREATE DATABASE dbname") #create db

or you can do that using mysql command-line client, as described in the comment to your question:

# run in the shell
mysql -u user -p password # often mysql -u root
mysql > CREATE DATABASE IF NOT EXISTS dbname;

In the any case, you only need to do this once, once the database is created, you can use it from your code, no need to do it each time. Obviously, you also need the mysql server installed and running on your system.

What I would suggest you to do is to learn the basics of SQL first (no python / SQLAlchemy) and learn how the things work directly in SQL (mostly how to create database and tables, how to design tables structure - data normalization, how to get the data - SELECT / JOIN, WHERE / ORDER, aggregate functions and HAVING). Once you know these things, it will be much easier to work with SQLAlchemy.

How to execute an sqlalchemy script with mysql to create a database

1 Answers1