CREATE TABLE in MySQL using sqlalchemy connection and displaying structure by query

Interactive Google Colab Practice Notebook

Practice creating tables, inserting sample records, inspecting structure using DESCRIBE, and executing queries interactively inside Google Colab.

View on GitHub

We will use Create Table to add student table to our database
We defined my_conn as connection object.

In all the codes below replace userid, password and database_name with your MySQL login and database details.

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

We are using student table. Use the code to create your sample student table.

Creating table ( We used escape char \ for line continuation )

try:    
    q = "CREATE TABLE IF NOT EXISTS `student` (\"
    "`id` int(2) NOT NULL AUTO_INCREMENT,\"
    "`name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',\"
    "`class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',\"
    "`mark` int(3) NOT NULL DEFAULT '0',\"
    "`sex` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male',\"
    "UNIQUE KEY `id` (`id`)\"
    ") ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"
    with my_conn.connect() as conn:
        conn.execute(text(q))
        conn.commit()
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

Table is created, we can check the structure of the table by using DESCRIBE.

with my_conn.connect() as conn:
    r_set = conn.execute(text("DESCRIBE student"))
    for row in r_set:
        print(row)

Output

('id', 'int(2)', 'NO', 'PRI', None, 'auto_increment')
('name', 'varchar(50)', 'NO', '', '', '')
('class', 'varchar(10)', 'NO', '', '', '')
('mark', 'int(3)', 'NO', '', '0', '')
('sex', 'varchar(6)', 'NO', '', 'male', '')

We can also use SHOW COLUMNS

with my_conn.connect() as conn:
    r_set = conn.execute(text("SHOW COLUMNS FROM student"))
    for row in r_set:
        print(row)

Let us add records

try:    
    q = "INSERT INTO `student` (`id`, `name`, `class`, `mark`, `sex`) VALUES\"
    "(1, 'John Deo', 'Four', 75, 'female'),\"
    "(2, 'Max Ruin', 'Three', 85, 'male'),\"
    "(3, 'Arnold', 'Three', 55, 'male'),\"
    "(4, 'Krish Star', 'Four', 60, 'female'),\"
    "(5, 'John Mike', 'Four', 60, 'female');"
    with my_conn.connect() as conn:
        conn.execute(text(q))
        conn.commit()
except SQLAlchemyError as e:
    error = str(e.__dict__['orig'])
    print(error)

Listing of records in a table

with my_conn.connect() as conn:
    r_set = conn.execute(text("SELECT * FROM student"))
    for row in r_set:
        print(row)

By using Tkinter we can create one GUI interface to store user entered inputs in MySQL table

Tkinter window to add user entered data to MySQL table More on Error handling

Python functions for MySQL Collect records Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

CREATE table in MySQL database using sqlalchemy

Interactive Google Colab Practice Notebook

Subhendu Mohapatra