CV from SQL Server and python-docx

PythonDocxCV.py

# -*- coding: utf-8 -*-
"""
Created on Wed Dec 27 18:38:55 2017

@author: garym
"""

#### Perform imports

from docx import Document
import pandas as pd
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Cm , Pt 
import datetime as datetime
import urllib
from sqlalchemy import create_engine
import pyodbc as py

### add function for multiple tabs

def addtab(num):
    count = 0 
    while count < num:
        p.add_run().add_tab()
        count= count+1 

### specify user id to extract from database
userId = 1 


#### set up connection string
### only need pyodbc for this one
conn_str = (
                   r'Driver={SQL Server};'
                   r'Server=localhost\SQLEXPRESS;'
                   r'Database=CVGen;'
                   r'Trusted_Connection=yes;'
                       )   
quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn = py.connect(conn_str)
cursor = cnxn.cursor()

### Gets todays date 
today = datetime.datetime.today().strftime('%d%m%Y')

### Extract data from database 
dfPerson = pd.read_sql("SELECT * FROM dbo.userprofile where userId = " + str(userId) , cnxn )
dfSkills = pd.read_sql("select skillName, [text] from skills s	inner join userskill u on s.skillid = u.skillid where userId = " + str(userId) , cnxn )
dfWork = pd.read_sql("SELECT  [UserId],[JobTitle],[FromDttm],[ToDttm],[CompanyName],[Description],[MainInd] FROM [CVGen].[dbo].[WorkHistory] where userId = " + str(userId) + ' order by MainInd desc' , cnxn )
dfEdu = pd.read_sql("select * from education where userId = " + str(userId), cnxn )
dfWeb = pd.read_sql("select * from website where userId = " + str(userId), cnxn )

### Extract Values for personal info into variables
Name = (dfPerson.get_value(0,'Name', takeable=False))
DOB = (dfPerson.get_value(0,'DOB2', takeable=False))
Address1 =  (dfPerson.get_value(0,'Address Line 1', takeable=False))
Address2 =  (dfPerson.get_value(0,'Address Line 2', takeable=False))
Email =  (dfPerson.get_value(0,'Email', takeable=False))
Mobile = (dfPerson.get_value(0,'Mobile', takeable=False))
Summary = (dfPerson.get_value(0,'Summary', takeable=False))
PostCode = (dfPerson.get_value(0,'PostCode', takeable=False))
Interest = (dfPerson.get_value(0,'Interest', takeable=False))

### create new document
document = Document()

### Set up Default Font
font = document.styles['Normal'].font
font.name = 'Arial'
font.size = Pt(10)

### Set up Margins
sections = document.sections
for section in sections:
    section.top_margin = Cm(1.25)
    section.bottom_margin = Cm(1.25)
    section.left_margin = Cm(1.75)
    section.right_margin = Cm(1.75)

### Add new paragraph - personal details at top
p = document.add_paragraph()
p.add_run(Name).bold = True
addtab(5)
p.add_run('DOB: ' + DOB)
# Adds line break
p.add_run().add_break() 
p.add_run(Address1)
addtab(3)
p.add_run('Email: ' + Email)
p.add_run().add_break() 
p.add_run(Address2 + ', ' + PostCode)
addtab(4)
p.add_run('Mobile: ' + Mobile)

### Add new heading 
document.add_heading('Profile', level=2)
### Add details and justify alignment 
p2 = document.add_paragraph(Summary)
p2.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.JUSTIFY 

### Add new heading
document.add_heading('Skills', level=2)

### Get unique list of Skills from dataframe skills 
dfSkillSections =  dfSkills.skillName.unique()

### loop through the skills
for skills in dfSkillSections:
    ### create a heading for each skill
    document.add_heading(skills, level=3)
    ### create a secondary dataframe with just skill details that match the headline skill
    df2 = dfSkills[dfSkills['skillName'] == skills]
    
    ### Loop through skill details adding a new bullet point for each 
    for row in df2.itertuples():
           document.add_paragraph(row[2], style='ListBullet')

### Save - final code does same for work history etc.
document.save(r'C:\Users\garym\Documents\New folder (2)\CVTest.docx')