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.