How to convert a database data in predefined format and generate output in text format-CodePudding

I am having a SQL database having the attributes First_Name; Last_Name; Sex; Address etc. For reporting to the authority I have an fixed format like

For First name: FN(no of characters in first name)First_Name

For example the first name Daniel should be like: FN05Daniel
For Bose it should be: LN04Bose

Is there any way using SSIS the same can be accomplished and the output can be generated in text format?

If not SSIS please suggest any probable mechanism to get the result. I am just learning coding so please don't mind if its a silly question.

CodePudding user response：

I don't get what you mean but this is how i use sqlite3

class Database():
    def __init__(self, path = ""):
        try:
            if path == "":
                with open("Database.db", "r") as Database:
                    pass
                self.path = "Database.db"
            else:
                with open(path   "/Database.db", "r") as Database:
                    pass
                self.path = path   "/Database.db"
        except:
            if path == "":
                with open("Database.db", "x") as Database:
                    pass
                self.path = "Database.db"
            else:
                if not os.path.exists(path):
                    os.mkdir(path)
                with open(path   "/Database.db", "x") as Database:
                    pass
                self.path = path   "/Database.db"
        self.Database = sqlite3.connect(self.path)
        self.Cursor = self.Database.cursor()
    def createSheet(self, name):
        self.Database.execute(
          f'''CREATE TABLE IF NOT EXISTS {name}(
          ID INT PRIMARY KEY
          );
          '''
        )
    def setVar(self, Sheet, Var, Value):
        try:
          self.Cursor.execute(
            f'''ALTER TABLE {Sheet} ADD COLUMN {Var} TEXT'''
          )
        except:
          pass
        self.Cursor.execute(
          f'''INSERT OR IGNORE INTO {Sheet} ({Var}) VALUES ("{Value}")'''
        )
        self.Cursor.execute(
          f'''UPDATE {Sheet} SET {Var} = "{Value}"'''
        )
        self.Database.commit()
    def readVar(self, Sheet, Var, Convert = str):
        DataGet = self.Database.execute(f'SELECT {Var} from {Sheet}').fetchone()
        return Convert(DataGet[0])

So imagine sqlite3 as Excel

The createSheet function actually creates a sheet in Excel (in sqlite3 is table)

The addVar function creates a column (which is the variable name, this variable is empty, which mean it doesn't have value) if not exists then it's will insert (put a value into the variable) or update (same as insert but it's update, insert function only work when you add an empty variable), value in sqlite3 is row

The readVar function read the value base on the variable name