CODEWITHSUNDEEP
pythonregex
rishi narian
rishi narian

Reputation: 373

How to print tablename and primary key from file.sql in python

I have a requirement to print tablename and primary key using the below program. But it is not working. Could you please help in solving this. I have used the regex pattern to find out table name and PRIMARY KEY data in the database Schema.

class ParseCreateFile:

    begin = re.compile(r"CREATE \s+ TABLE \s+'((?:[^']|'')*)'.+(PRIMARY \s+ KEY\s? \([^)]*\))", re.IGNORECASE | re.VERBOSE | re.DOTALL)

    def __init__(self,fileName):
        self.fileName = fileName
        self.readFromFile()

    def readFromFile(self):
        try:
            file = open(self.fileName,'r')
            fileData = file.read()
            file.close()
            self.parse(fileData)
        except IOError:
            print 'Unable to read file %s' % self.fileName

    def parse(self,rawData):
        self.list = []
        while rawData:
          mo = self.begin.search(rawData)
          tablename, fil = mo.groups()
          print tablename, fil
          if mo is None:
            break
          rawData = rawData[mo.end():]

    if __name__ == '__main__':
        print "only the main classes"
        file = ParseCreateFile('file2.sql')

file2.sql

CREATE TABLE 'x'(
  'X' integer
  PRIMARY KEY(dn)
  'Y' short);
CREATE TABLE 'y'(
  'X1' integer
  PRIMARY KEY(dn1, dn2)
  'Y1' short);

Expected Out:
x: PRIMARY KEY(dn)
y: PRIMARY KEY(dn1, dn2)

Upvotes: 1

Views: 429

Answers (1)

Akram Parvez
Akram Parvez

Reputation: 451

I edited the regex, and used the findall.

class ParseCreateFile:

    begin = re.compile(r"CREATE TABLE[ ']*([^ ']+)[ ']*[(][^/;]+(PRIMARY KEY.*)[^/;]+;", re.IGNORECASE)

    def __init__(self,fileName):
        self.fileName = fileName
        self.readFromFile()

    def readFromFile(self):
        try:
            file = open(self.fileName,'r')
            fileData = file.read()
            file.close()
            self.parse(fileData)
        except IOError:
            print 'Unable to read file %s' % self.fileName

    def parse(self,rawData):
        self.list = []
        found = self.begin.findall(rawData)
        for i in found:
            print i[0], i[1]


if __name__ == '__main__':
    print "only the main classes"
    file = ParseCreateFile('file2.sql')

Upvotes: 1

Related Questions