Create Table
To create table in database you have to used ‘CREATE TABLE’ statement
import mysql.connector mycon = mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() mycur.execute("CREATE TABLE students (name VARCHAR(50), address VARCHAR(50),roll_no VARCHAR(20))")
Output
C:\Users\laptop>python demo.py
Checked Table exists
import mysql.connector mycon=mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() mycur.execute("SHOW TABLES") for x in mycur: print(x)
Output
C:\Users\My Name>python demo.py ('students',)
Primary Key
When creating the table in a database we make sure that we create the primary key in the table.
Primary key is the unique column in tables in which entered information does not match with any other information in the same column, in easy word Primary key is created for making each information unique.
We use the statement “INT AUTO_INCREMENT PRIMARY KEY” to declare the primary key.
import mysql.connector mycon=mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() mycur.execute("CREATE TABLE student (roll_no INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), address VARCHAR(50),mobile INT)")
Output
C:\Users\laptop>python demo.py
If table already exist used “Alter” keyword
import mysql.connector mycon=mysql.connector.connect( host="localhost", user="root", passwd="1234", database="college" ) mycur=mycon.cursor() mycur.execute("ALTER TABLE students ADD COLUMN sr_no INT AUTO_INCREMENT PRIMARY KEY)