Automating my DBMS assignment with Python

Last week was kind of boring for me. My phone slipped from my hand and broke 😥 (but the best part is I’m getting a new one 😍). So without a phone I had loads of time in my hand so when we received this DBMS assignment from our instructor and I decided to do something out of the box with it.

As an introduction to the Database Management System Course, our first assignment consisted of making loads of tables (actually 11) and inserting records into them.

The CREATE TABLE statement is used to create a table in SQL database:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
….
);

Whereas to insert values into the same table the syntax looks like this:

INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);

So, we are to make 11 tables with an average of say 10 records in each amounting to 110 record entries to be inserted manually. It was then I decide to automate it with python :)

I wrote a simple python script to parse entire tables from the docx file using python-docx library locally into tuples. Then I used mysql-connector-python (MYSQL driver) library to connect to the SQL service running on my machine using proper credentials. And then I scripted the table creation part and corresponding records addition to individual tables and Viola we are done !!!

from docx.api import Document
import mysql.connector
document = Document('table.docx')
table_names = ["Depositor_Table","Item_Table","Customer_Table","Salesman_Table","Sales_Order_Table","Branch_Table","Customer_new_Table","Loan_Table","Borrower_Table","Account_Table","Depositor_new_Table"]
 
for i in range(len(document.tables)):
table = document.tables[i]
 data = []
 keys = None
 for j, row in enumerate(table.rows):
     text = (cell.text.encode('utf-8') for cell in row.cells)
     if j == 0:
  keys = tuple(text)
  continue
     row_data = tuple(text)
     data.append(row_data)
     
 mydb = mysql.connector.connect(
   host="localhost",
   user="francesco",
   passwd="some_pass",
   database="test"
 )
 cursor = mydb.cursor()
 sql_item ="CREATE TABLE " + table_names[i] +  " ("
 
 for j in range(len(keys)):
  header = keys[j]
  repeat = str(header).strip().replace(" ","").replace("\n","") + " CHAR(20) NOT NULL,"
  if j == (len(keys)-1):
   repeat = str(header) + " CHAR(20) NOT NULL)"
  sql_item += repeat
 cursor.execute(sql_item)
 insert_sql_header = "INSERT INTO " + table_names[i] + " ("
 sql_item = ""
 for j in range(len(keys)):
  header = keys[j]
  repeat = str(header).strip().replace(" ","").replace("\n","") + ","
  if j == (len(keys)-1):
   repeat = str(header)
  sql_item += repeat
sql_header = ") VALUES ("
 sql_repeat = ""
if len(keys)==1:
  sql_repeat = "%s)"
else:
  for j in range(len(keys)-1):
   sql_repeat+="%s, "
   
   sql_repeat += "%s)"
insert_sql = insert_sql_header + sql_item + sql_header + sql_repeat 
 
 for j in range(len(keys)):
  val = data[j]
  cursor.execute(insert_sql, val)
mydb.commit()
print "Table " + str(i) + " complete..."

A moment of truth 😍

The codes along with detailed steps are available in my github repo (here).

Some problems I ran into:

• I got stuck at parsing the column headers 😢 for at least an hour but eventually figured out the bug.
• Initially I wrote the entire script in python3 then I learnt that mysql.connector has some compatibility issues with python2 so I had to revert back the entire script to python2.

‣‣‣You just read frostbite. Hope you enjoyed it…