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

Question

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)

Possible duplicate of Need regex expression to get only Tably name and primary key in python — Akram Parvez
– Akram Parvez, Commented Feb 25, 2016 at 6:49
With simple string, things are working fine. But the same is not working when i am trying with the files — rishi narian
– rishi narian, Commented Feb 25, 2016 at 7:07

Akram Parvez · Accepted Answer · 2016-02-25 07:36:40Z

1

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')

edited Feb 25, 2016 at 7:36

answered Feb 25, 2016 at 7:22

Akram Parvez

4513 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rishi narian Over a year ago

With the above program, the output is shown as "only the main classes". It is not working. The desired output is not shown.

Akram Parvez Over a year ago

Removed re.VERBOSE | re.DOTALL

rishi narian Over a year ago

Thanks for your answer

rishi narian Over a year ago

Hi Akram, if i have tables like this CREATE TABLE 'x'( 'X' integer PRIMARY KEY(dn)); CREATE TABLE 'y'( 'X1' integer PRIMARY KEY(dn1, dn2)); The above logic doesn't work

Akram Parvez Over a year ago

Hopefully this will work for everything you had till now, you need to fix the regex. re.compile(r"CREATE TABLE[ ']*([^ ']+)[ ']*[(][^/;]+(PRIMARY KEY.*?\))[^/;]+;", re.IGNORECASE)

|

Collectives™ on Stack Overflow

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

file2.sql

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

file2.sql

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related