Creating MySQL database and table Flask Tutorial

In this tutorial, we're going to be creating our user database, which we need for storing usernames and passwords from when users actually register.

If you have been following along with this series, then you already have the MySQL server and the MySQL client installed. If not, you need to get those. When you get those, you will also set up the root MySQL user and password. Again, if you have been following along, then you already set this up. The MySQL root user and the server root user is separate, and they can have different passwords. They are different users. You can have them as the same password, though this isn't recommended for security.

Now, let's log in, create a database, and create our table!

mysql --user=root -p

Here, we're calling on mysql to login as the root user. You will be prompted to enter your password after hitting enter.

Now we are in the MySQL client, designated by the leading mysql>. Try typing the following:


This will output all of the existing MySQL databases that you have.

A common misunderstanding is what a database is, compared to a table. Most people's conception of a database is actually more accurately a table. So a database is what stores tables. The tables store the data. A database can contain just one table, but the bottom line is that it is actually the table that contains the information. A table, for your typical database is very much like a spreadsheet, with simple columns and rows. There are more advanced versions of tables and databases, which offer more dimensions and more options, though the typical database is just rows by columns.


This will output all of the existing MySQL databases that you have.

CREATE DATABASE pythonprogramming;

This will create a new database, called pythonprogramming.

It is convention to put all SQL language in ALL-CAPS. This is because you are going to be typing SQL in-line with your Python. Even to the trained eye, it can become hard to discern what is Python and what is SQL. This is true across all programming languages that incorporate SQL.

After you typed the CREATE DATABASE pythonprogramming; (first noting and remembering that you will always finish your lines with semi-colons) notice the output:

Query OK, 1 row affected (0.00 sec)

This is fairly standard output syntax that you will see, and it will be useful to always read it. If there is an error, you will get an error, but sometimes you think you may only be affecting one row, but instead you affected millions. We will talk more about this soon, but always remember, there is no 'undo' in MySQL. What's done is done. We will talk later about some "best practices" for ideas about how to

Now that we have our database, we want a table, we're going to call it users.

First, however, we need to enter the database that we want to use for this, so we need to do:

USE pythonprogramming;

It's easy to forget this step from time to time, especially since you will mostly interact with SQL via your programs, not by hand.

CREATE TABLE users (uid INT(11) AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), password VARCHAR(100), email VARCHAR(50), settings VARCHAR(32500), tracking VARCHAR(32500), rank INT(3));

This will create a new table within the database that we're using, which is pythonprogramming, called users.

Now this is probably not going to be the most efficient table, and we might be better off with a different type of data than VARCHAR for the settings and tracking, but VARCHAR should be just fine for our uses. If you're expecting a database with millions of rows of data that might be simultaneously accessed, then you will want to test many datatypes. There are many forms for number data like FLOAT, INT, BIG-INT...etc, and many for text, like BLOB.

If you ever happen to forget about the columns or data types in your table, you can do the following:


This will output the table structure.

You can also run SQL queries here, like INSERT, SELECT, UPDATE, DELETE, and all that fun stuff, but we're more interested in doing this via our program. You can also create databases and tables in your Flask application. You can do this by checking to see if the table exists and, if not, create it. I find it's easier to just do it by hand, but if you are creating some sort of module for user-systems, then you would want to include the creation of the initial table for the people who download your module, for example. If you find yourself as a freelance web developer, doing it over and over, you may want a script for yourself that does it too!

All that said, we're more interested now in getting our program to connect to the MySQL database for insertion purposes. For now, type QUIT to exit the MySQL client in your server.

There exists 1 challenge(s) for this tutorial. for access to these, video downloads, and no ads.

The next tutorial:

  • Introduction to Practical Flask
  • Basic Flask Website tutorial
  • Flask with Bootstrap and Jinja Templating
  • Starting our Website home page with Flask Tutorial
  • Improving the Home Page Flask Tutorial
  • Finishing the Home Page Flask Tutorial
  • Dynamic User Dashboard Flask Tutorial
  • Content Management Beginnings Flask Tutorial
  • Error Handling with Flask Tutorial
  • Flask Flash function Tutorial
  • Users with Flask intro Tutorial
  • Handling POST and GET Requests with Flask Tutorial
  • Creating MySQL database and table Flask Tutorial
  • Connecting to MySQL database with MySQLdb Flask Tutorial
  • User Registration Form Flask Tutorial
  • Flask Registration Code Tutorial
  • Finishing User Registration Flask Tutorial
  • Password Hashing with Flask Tutorial
  • Flask User Login System Tutorial
  • Decorators - Login_Required pages Flask Tutorial
  • Dynamic user-based content Flask Tutorial
  • More on Content Management Flask Tutorial
  • Flask CMS Concluded Flask Tutorial
  • The Crontab Flask Tutorial
  • Flask SEO Tutorial
  • Flask Includes Tutorial
  • Jinja Templating Tutorial
  • Flask URL Converters Tutorial
  • Flask-Mail Tutorial for email with Flask
  • Return Files with Flask send_file Tutorial
  • Protected Directories with Flask Tutorial
  • jQuery with Flask Tutorial
  • Pygal SVG graphs with Flask Tutorial
  • PayPal with Flask Web Development Tutorial
  • Securing your Flask website with SSL for HTTPS using Lets Encrypt