CREATE table in MySQL database using sqlalchemy

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


plus2net.com



Post your comments , suggestion , error , requirements etc here





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