Python – MySQL – Create Table

In MySQL, a table is a collection of data or records that are stored in a structured format. Each table has a name that uniquely identifies it within a database. To create a table in MySQL using Python, you need to establish a connection between a MySQL database and Python first. Once that is done, you can use Python to send SQL commands to MySQL to create tables or manipulate data.

Prerequisites

In order to create a table in MySQL using Python, you’ll need the following:

• Python 3.6 or newer
• MySQL Server 5.6 or newer
• The mysql-connector-python module

Step 1: Install the mysql-connector-python Module

Before you can start using Python to interact with MySQL, you need to install the python MySQL connector. This is a driver that enables Python to connect to a MySQL database. You can install this module using pip, the Python package manager by running:

pip install mysql-connector-python

If you don’t have pip installed, you can install it from the command line with:

sudo apt-get install python3-pip

or

sudo yum install python3-pip

Step 2: Connect to MySQL

You need to establish a connection with MySQL before creating a table. Let’s take a look at an example:

import mysql.connector

# Establish a connection to a MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

print(mydb)

Make sure to replace the yourusername, yourpassword and mydatabase with your own database credentials.

In the above code, you create a connection object using the mysql.connector.connect() method, passing in the four connection parameters required to connect to a MySQL database:

• host: The IP address or DNS name of the MySQL server. In this case, it’s your local machine – “localhost”.
• user: The MySQL user to connect as.
• password: The password for the MySQL user, if one is required.
• database: The name of the MySQL database to connect to.

Once the connection is established, it’s stored in a variable called mydb. The print() function is used to verify that the connection was successful. This will output a string representation of the connection object, which includes information such as the server host, server port, and protocol used.

Step 3: Create a Table

Now that you’re connected to your MySQL database, let’s create a table. For this tutorial, we’re going to create a table called “users” that will store user information. Each user will have an ID, a first name, a last name, and an email address.

To create this table, execute the following SQL command:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email VARCHAR(255)
);

This will create a table named “users” with four columns:

• id: An integer that is automatically incremented for each new record. This will be the primary key for the table.
• first_name: A string that will store the user’s first name.
• last_name: A string that will store the user’s last name.
• email: A string that will store the user’s email address.

Now, let’s use Python to create this table.

import mysql.connector

# Establish a connection to a MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# Execute the SQL command to create a table
mycursor.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255), email VARCHAR(255))")

# Test that the table was created
mycursor.execute("SHOW TABLES")
for x in mycursor:
  print(x)

The code above does the following:

• Imports the mysql.connector module
• Establishes a connection to your MySQL database
• Creates a cursor object, which is used to traverse the database
• Executes an SQL query to create a table called “users”
• Executes a second SQL query to test that the table exists by printing all tables in the database

You can verify that the “users” table was created by running the code above.

Step 4: Insert Data into the Table

Now that you have created a table, you can insert data into it. For example:

import mysql.connector

# Establish a connection to a MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# Insert a record into the table
sql = "INSERT INTO users (first_name, last_name, email) VALUES (%s, %s, %s)"
val = ("John", "Doe", "[email protected]")
mycursor.execute(sql, val)

# Commit the change to the database
mydb.commit()

print(mycursor.rowcount, "record inserted.")

The code above does the following:

• Imports the mysql.connector module
• Establishes a connection to your MySQL database
• Creates a cursor object, which is used to traverse the database
• Executes an SQL query to insert a new record into the “users” table
• Commits the change to the database, which saves the record permanently
• Prints the number of records that were inserted (in this case, it should be 1)

You can modify the val variable to insert different data into the table. Simply replace the values in the tuple with the data you want to insert.

Conclusion

In this tutorial, you have learned how to create a table in MySQL using Python. You used the mysql.connector module to establish a connection to your MySQL database, created a table using an SQL command, and inserted data into the table using Python. Armed with this knowledge, you can now create and manage MySQL tables from Python with confidence.