2. dbconnect.pyΒΆ

  • This file is used to create all the database tables using sqlalchemy

    • import sqlAlchemy to create tables
    • import ElemnentTree to parse the xml file.
    • import declarative base class from sqlalchemy for mapping a classes.
    • creating an empty list of engines.
    • engine in this analogy is a connection maintained as a session.
    • so every time a new client connects to the rpc server,
    • a new engine is appended to the list and the index returned as the id.
    engines = []
    session = sessionmaker()
    from sqlalchemy import create_engine, func, select, literal_column
    from sqlalchemy import orm
    from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, select, Text, DECIMAL, Enum
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker, scoped_session
    from sqlalchemy.types import Numeric, TIMESTAMP, Enum
    from xml.etree import ElementTree as et
    import os
    import datetime, time
    from time import strftime
    from sqlalchemy import *
    from types import *
    from sqlite3 import dbapi2 as sqlite
    
  • def getOrgList()

    • This function create opens the configuration file abt.xml (path : opt/abt)
    • used xml.etree to cerate xml file and parsing it .
    • Write root node <abt> </abt>.
    • if any organisation present then and gets the list of all organisations registered on the server.
    def getOrgList():
    
        if os.path.exists("/opt/abt/abt.xml") == False:
            print "file not found trying to create one."
            try:
                os.system("touch /opt/abt/abt.xml")
                print "file created "
                os.system("chmod 722 /opt/abt/abt.xml")
                print "permissions granted "
            except:
                print "the software is finding trouble creating file."
                return False
            try:
                abtconf = open("/opt/abt/abt.xml", "a")
                abtconf.write("<abt>\n")
                abtconf.write("</abt>")
                abtconf.close()
            except:
                print "we can't write to the file, sorry!"
                return False
        #opening the abt.xml file by parsing it into a tree.
        abtconf = et.parse("/opt/abt/abt.xml")
        #now since the file is opened we will get the root element.
        abtroot = abtconf.getroot()
        #we will now extract the list of children (organisations) into a variable named orgs.
        orgs = abtroot.getchildren()
        return orgs
    
  • def getConnection(queryParams):
    • The getConnection function will actually establish connection and
    • return the id of the latest engine added to the list.
    • first check if the file exists in the given path.
    • if this is the first time we are running the server then we need to create the abt.xml file.
    def getConnection(queryParams):
    
         dbname = "" #the dbname variable will hold the final database name for the given organisation.
         orgs = getOrgList() #we will use org as an iterator and go through the list of all the orgs.
    
         for org in orgs:
             orgname = org.find("orgname")
             financialyear_from = org.find("financial_year_from")
             financialyear_to = org.find("financial_year_to")
             print orgname.text,queryParams[0],financialyear_from.text,queryParams[1],financialyear_to.text,queryParams[2]
             if orgname.text == queryParams[0] and financialyear_from.text == queryParams[1] and financialyear_to.text == queryParams[2]:
                 #print "we r in if"
                 dbname = org.find("dbname")
                 database = dbname.text
    
             else:
                 print "orgnisationname and financial year not match"
         global engines #the engine has to be a global variable so that it is accessed throughout the module.
         stmt = 'sqlite:////opt/abt/db/' + database
         engine = create_engine(stmt, echo=False) #now we will create an engine instance to connect to the given database.
         engines.append(engine)  #add the newly created engine instance to the list of engines.
         return engines.index(engine) #returning the connection number for this engine.
    
  • Mapping classes into tables using Declarative Class:

    • Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base - this is known as the declarative base class.
    • Our application will usually have just one instance of this base in a commonly imported module. We create the base class using the declarative_base() function, as follows:
    Base = declarative_base()
    
    class Account(Base):
        __tablename__ = "account"
        accountcode = Column(String(40), primary_key=True)
        groupcode = Column(Integer, ForeignKey("groups.groupcode"), nullable=True)
        subgroupcode = Column(Integer, ForeignKey("subgroups.subgroupcode"), nullable=True)
        accountname = Column(Text, nullable=False)
        openingbalance = Column(Numeric(13, 2))
        openingdate = Column(TIMESTAMP)
        balance = Column(Numeric(13, 2))
    
    
        def __init__(self, accountcode, groupcode, subgroupcode,
        accountname, openingbalance, openingdate, balance):
            self.accountcode = accountcode
            self.groupcode = groupcode
            self.subgroupcode = subgroupcode
            self.accountname = accountname
            self.openingbalance = openingbalance
            self.openingdate = openingdate
            self.balance = balance
    
    
    account_table = Account.__table__
    
    orm.compile_mappers()
    
    • same way go for another tables.
    • For more info go to Link.

This Page