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

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 ;"
    my_conn.execute(q)
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.

r_set=my_conn.execute("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

r_set=my_conn.execute("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'),\
    (6, 'Alex John', 'Four', 55, 'male'),\
    (7, 'My John Rob', 'Five', 78, 'male'),\
    (8, 'Asruid', 'Five', 85, 'male'),\
    (9, 'Tes Qry', 'Six', 78, 'male'),\
    (10, 'Big John', 'Four', 55, 'female'),\
    (11, 'Ronald', 'Six', 89, 'female'),\
    (12, 'Recky', 'Six', 94, 'female'),\
    (13, 'Kty', 'Seven', 88, 'female'),\
    (14, 'Bigy', 'Seven', 88, 'female'),\
    (15, 'Tade Row', 'Four', 88, 'male'),\
    (16, 'Gimmy', 'Four', 88, 'male'),\
    (17, 'Tumyu', 'Six', 54, 'male'),\
    (18, 'Honny', 'Five', 75, 'male'),\
    (19, 'Tinny', 'Nine', 18, 'male'),\
    (20, 'Jackly', 'Nine', 65, 'female'),\
    (21, 'Babby John', 'Four', 69, 'female'),\
    (22, 'Reggid', 'Seven', 55, 'female'),\
    (23, 'Herod', 'Eight', 79, 'male'),\
    (24, 'Tiddy Now', 'Seven', 78, 'male'),\
    (25, 'Giff Tow', 'Seven', 88, 'male'),\
    (26, 'Crelea', 'Seven', 79, 'male'),\
    (27, 'Big Nose', 'Three', 81, 'female'),\
    (28, 'Rojj Base', 'Seven', 86, 'female'),\
    (29, 'Tess Played', 'Seven', 55, 'male'),\
    (30, 'Reppy Red', 'Six', 79, 'female'),\
    (31, 'Marry Toeey', 'Four', 88, 'male'),\
    (32, 'Binn Rott', 'Seven', 90, 'female'),\
    (33, 'Kenn Rein', 'Six', 96, 'female'),\
    (34, 'Gain Toe', 'Seven', 69, 'male'),\
    (35, 'Rows Noump', 'Six', 88, 'female');"
    my_conn.execute(q)
except SQLAlchemyError as e:
        error = str(e.__dict__['orig'])
        print(error)

Listing of records in a table

r_set=my_conn.execute("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

Subscribe to our YouTube Channel here

CREATE table in MySQL database using sqlalchemy

Subscribe