1

I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy. For starters I created a python script which reads from a file and then creates a table in my database which has inside the contents of the first file. I want now to read a new file everyday, they are all of the same format, and then to update my database accordingly. The code for my first file is as follows:

import re
import sqlalchemy
from sqlalchemy import Table, Column, Integer, String, Text, Date, MetaData#, ForeignKey
from sqlalchemy.sql import select
from Tkinter import Tk
from tkFileDialog import askopenfilename

metadata = MetaData()

patient_data = Table('Patient_Data', metadata,
    Column('DepartmentCode', Text), # replace to String!
    Column('Room', Text),
    Column('Bed', String(1)),
    Column('Name', Text),
    Column('Surname', Text),
    Column('MiddleName', Text),
    Column('Spec', Text),
    Column('PatientNr', String(9), primary_key=True),
    Column('DOB', Date),
    Column('DOA', Date),
    Column('OpnKls', Text)
)

mysql_engine = sqlalchemy.create_engine('mysql://root:xxxxxxxx@localhost/test')
mysql_connection = mysql_engine.connect()

metadata.create_all(mysql_engine)

Tk().withdraw() # we don't want a full GUI, so keep the root window from appearing
filename = askopenfilename() # show an "Open" dialog box and return the path to the selected file
#print(filename)

## Open the file with read only permit
#f = open('131213-XSEPR1-LOCA4202-PR10461', "r")
f = open(filename, "r")
add_to_db = []
## use readlines to read all lines in the file
## The variable "lines" is a list containing all lines
lines = f.readlines()

for l in lines:
    if re.match('[\s+$]', l):
        continue
    tokens = [l[0:4],l[7:9],l[13:14],l[17:29].rstrip(),l[30:42].rstrip(),l[43:55].rstrip(),l[56:59],l[61:70],l[72:82],l[84:94],l[97:99]]
    if re.match('[A-Z][0-9][A-Z][A-Z]',tokens[0]) and re.match('([0-9]){2}.([0-9]){2}.([0-9]){3}',tokens[7]):
        row_to_add = {
            'DepartmentCode': tokens[0],
            'Room': tokens[1],
            'Bed': tokens[2],
            'Name': tokens[3],
            'Surname': tokens[4],
            'MiddleName': tokens[5],
            'Spec': tokens[6],
            'PatientNr': tokens[7],
            'DOB': tokens[8],
            'DOA': tokens[9],
            'OpnKls': tokens[10]
            }
        add_to_db.append(row_to_add)

## close the file after reading the lines.
f.close()

mysql_connection.execute(patient_data.insert(), add_to_db)

I am really new to python and this I did with the help of friends. Could you please anyone help me on how to read a second file of the same format within the database created from the first file ?

Thank you in advance for anyones help.

haltabush
  • 4,508
  • 2
  • 24
  • 41

0 Answers0