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
		

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

SHOW DATABASES;
		

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.

SHOW DATABASES;
		

CREATE DATABASE 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:

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));
		

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:

DESCRIBE users;

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.

The next tutorial: Connecting to MySQL database with MySQLdb Flask Tutorial