Python MySQL CREATE TABLE

Description

The Python MySQL CREATE TABLE command creates a new table of a given name inside a database. A table consists of rows and columns. At the time of creation, each column must be declared properly, with it’s name and datatype.

Tables are where all the data in a database is really stored. You can conduct several operations on Tables like INSERT, REMOVE and SELECT to manipulate the rows of values inside them. All Tables are empty upon creation.

CREATE TABLE table_name

Fun Fact: The SQL syntax never changes. Whichever language you use MySQL in, the syntax shown above will always remain the same. Even if you directly use SQL in a database, the syntax is as shown above.

Example

Remember to import the mysql.connector and create a connection to your database as shown below before attempting anything. Follow up by creating a cursor object which we’ll be using to manipulate to the database.

We’ve assigned the name and gender the VAR(255) data type. You can think of VARCHAR as the equivalent to strings. 255 determines the maximum size of the variable.

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="username",
  passwd="password",
  database = "testdb")

cursor = db.cursor()
cursor.execute("CREATE TABLE student 
              (name VARCHAR(255) ,
               age INT, 
               gender VARCHAR(255) )")

Creating a Primary Key

Primary key’s are an essential part of any database. A primary key is a unique column in a table, and it’s value is unique or each record. For instance, roll numbers for students is an example of a primary key, as each student has a unique roll number. Primary keys are useful in ordering or searching through records as only one possible record can exist at one primary key value.

To add a primary to key to a Database, only a small addition is required. Adding the words PRIMARY KEY after a column will turn it into a Primary key column, while adding AUTO_INCREMENT will cause the id to increment automatically as records are added.

cursor.execute("CREATE TABLE student 
              (id INT AUTO_INCREMENT PRIMARY KEY,
               name VARCHAR(255) ,
               age INT, 
               gender VARCHAR(255) )")

Altering a Table

If you’ve made a Table with a few columns, and suddenly decide you want to add another column, instead of creating a new table, you can simply alter the current one.

Run the following code to alter the table.

cursor.execute("ALTER TABLE student ADD COLUMN address VARCHAR(255)")

Show all Tables

You can use the SHOW TABLES command in MySQL to get a list of all the Tables in Database. For this to work however, you need to be connected to a specific database. This command will only show all the tables in one database, not all.

cursor.execute("SHOW TABLES")

#Method1
for x in cursor:
  print(x)

#Method2
print(cursor.fetchall())

This marks the end of our Python MySQL CREATE TABLE Article. Let us know if you have any suggestions or corrections in this Article to make. Contributions to help grow CodersLegacy are more than welcome.

Use this link to head back to main Python MySQL page: link