Create and List Table-Python

What is a table in a database?

• A table in a database is a collection of data organized in the form of rows and columns.
• Each row represents a single record or tuple, and each column represents an attribute or field of that record.
• The columns, also known as fields or attributes, have specific data types such as integer, string, or date and define the type of data that can be stored in that column.
• A table can also have constraints such as primary key, foreign key, and unique key which help to maintain the integrity of the data in the table.

To create a table in a MySQL database using Python, you can use the mysql-connector-python library, which provides a Python API for connecting to and interacting with MySQL databases.

SQL Query to Create Table:

The basic syntax for creating a table in SQL is:

CREATE TABLE table_name (
  column1 data_type constraint,
  column2 data_type constraint,
  column3 data_type constraint,
  ...
  constraint
);

• CREATE TABLE is the keyword that tells the database system to create a new table.
• table_name is the name of the table you want to create.
• column1, column2, column3 are the names of the columns in the table and data_type is the type of data that the column can hold (e.g. INT, VARCHAR, DATE).
• constraint is optional and is used to specify constraints on the column such as primary key, foreign key, not null, etc.

For Example:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  salary INT,
  department VARCHAR(255)
);

List existing Tables in a Database:

To list all existing tables in a database, you can use the SHOW TABLES command in SQL.

Here is the general syntax:

SHOW TABLES;
 or
SHOW TABLES [FROM database_name];

The SHOW TABLES command will return a list of all tables in the current database (if the FROM clause is not used), or in the specified database (if the FROM clause is used).

For example, to list all tables in a database called “mydb”:

SHOW TABLES FROM mydb;

Table with Primary Key:

What is Primary a Key?

A primary key is a column or set of columns that uniquely identifies each row in a table. In other words, it is a unique identifier for each record in a table. In SQL, you can specify a column as a primary key by using the PRIMARY KEY constraint.

Here is an example of how to create a table called “employees” with a primary key column “id”:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  salary INT,
  department VARCHAR(255)
);

In this example, the column “id” is defined as the primary key for the table. The PRIMARY KEY constraint enforces the uniqueness of the “id” column and prevents duplicate values.

It’s important to note that a table can have only one primary key. If you have multiple columns and you want to use them as primary key, you can use a composite primary key that uses multiple columns. You can use PRIMARY KEY (col1, col2, ...) syntax to define a composite primary key.

A primary key can also be a combination of one or more columns, in this case, is called a composite primary key.

CREATE TABLE table_name (
  col1 data_type constraint,
  col2 data_type constraint,
  col3 data_type constraint,
  ...
  PRIMARY KEY (col1, col2, ...)
);

Describe the Table:

To view the structure of a table, including the column names, data types, and constraints, you can use the DESCRIBE or DESC command in SQL.

Here is the general syntax:

DESC table_name;

The DESCRIBE or DESC command will return information about the specified table, including the column names, data types, and constraints.

For example, to view the structure of a table called “employees”:

DESCRIBE employees;

The output will show the name of the columns, their data type, if they can be null or not and other information about the table like default value, index and constraints.

Note: also read about Create Database-Python MySQL

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py