CREATE table in MySQL database using sqlalchemy


Youtube Live session on Tkinter

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)
Tkinter window to add user entered data to MySQL table More on Error handling
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer