OverIQ.com

  1. Home
  2. MySQL Connector/Python Tutorial
  3. Creating Tables using Connector/Python

Creating Tables using Connector/Python

Last updated on July 27, 2020

The code in the following listing creates a database and two tables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
import mysql.connector
from mysql.connector import errorcode
from collections import OrderedDict

db = mysql.connector.connect(option_files='my.conf', use_pure=True)

cursor = db.cursor()

db_name = 'blog'

tables = OrderedDict()

tables['category'] = '''
create table category
(
  id int not null auto_increment primary key,
  name varchar(50) not null
)
'''

tables['post'] = '''
create table post
(
  id int not null auto_increment PRIMARY KEY,
  title varchar(200) not null,
  content text not null,
  date TIMESTAMP not null,
  category_id int not null,
  FOREIGN key (category_id) REFERENCES category(id)
)
'''


def create_db(cursor):
    try:
        cursor.execute("create database {}".format(db_name))
        print("Database created.")
    except mysql.connector.Error as err:
        print("Database creation failed:", err)
        exit(1)


try:
    db.database = db_name
    print('Database {} already exist.'.format(db_name))
except mysql.connector.Error as err:
    # database doesn't exist, create one
    if errorcode.ER_BAD_DB_ERROR == err.errno:
        create_db(cursor)
        db.database = db_name


for k, v in tables.items():
    try:
        cursor.execute(v)
        print('Table {} created.'.format(k))
    except mysql.connector.Error as err:
        if errorcode.ER_TABLE_EXISTS_ERROR == err.errno:
            print('Table {} already exists.'.format(k))


cursor.close()
db.close()

Expected Output:

1
2
3
Database created.
Table category created.
Table post created.

How it works:

  1. In line 5-10, we define tables using the OrderedDict dictionary instead of a regular dictionary, this is because the regular dictionary doesn't preserve the order of elements.

  2. In line 11, we try to change the database using the database property of the connection object. If the database doesn't exist we call create_db() to create the database.

  3. In line 12-20, we create tables by looping over the items in the tables dictionary.

Try deleting the post table and run the script again. This time you will get the following output:

1
2
3
Database blog already exists.
Table category already exists.
Table post created.

MySQL Connector/Python Tutorial

Recent Posts