Need regex expression to get only Tably name and primary key in python

Question

May this question is silly, but i am not able to generate the regex expression to fetch table Name and PRIMARY KEY.

TABLE:

CREATE TABLE 'dhcpr_dhcprelayinterface'  (
        'vrId' integer default 0,
        'ifName' string ,
        PRIMARY KEY(ifName,vrId),
        FOREIGN KEY (vrId) REFERENCES 'vr_vr'(vrId) ON DELETE CASCADE ON UPDATE CASCADE);

I am using: begin = re.compile(r"CREATE TABLE[ \"]*([^ \"]+)[ \"]*[(]([^/;]+)[/;]",re.IGNORECASE) to fetch all table name and data.

But i would need data only with table name and PRIMARY KEY.

Expected Output:

dhcpr_dhcprelayinterface
PRIMARY KEY(ifName,vrId)

score 2 · Answer 1 · answered Feb 25 '16 at 04:14

This solution takes care of some issues you seem not worried about (but which are good to worry about), e.g., SQLite allows you to write escaped ' as '', and there may be any number of spaces, even newlines, between CREATE and TABLE, and between PRIMARY, KEY, and (:

s = """\
CREATE TABLE 'dhcpr_dhcprelayinterface'  (
    'vrId' integer default 0,
    'ifName' string ,
    PRIMARY KEY(ifName,vrId),
    FOREIGN KEY (vrId) REFERENCES 'vr_vr'(vrId)
    ON DELETE CASCADE ON UPDATE CASCADE);
"""

pattern = """
    CREATE \s+ TABLE \s+
    '((?:[^']|'')*)'      # allows escaped single quote
    .+                    # stuff between table name and primary key
    (PRIMARY \s+ KEY\s? \([^)]*\))
"""
mo = re.search(pattern, s, re.IGNORECASE | re.VERBOSE | re.DOTALL)
print(mo.groups())

Output:

('dhcpr_dhcprelayinterface', 'PRIMARY KEY(ifName,vrId)')

score 1 · Answer 2 · edited May 23 '17 at 12:08

I'm sure you can solve it with regular expressions or sqlparse, but here is a "fun" way of approaching the problem just for educational purposes - using sqlite3 in memory database - actually create the table and get the table_name from the sqlite_master internal table and primary key columns from the PRAGMA table_info:

import sqlite3

query = """
CREATE TABLE 'dhcpr_dhcprelayinterface'  (
        'vrId' integer default 0,
        'ifName' string ,
        PRIMARY KEY(ifName,vrId),
        FOREIGN KEY (vrId) REFERENCES 'vr_vr'(vrId) ON DELETE CASCADE ON UPDATE CASCADE);
"""

db = sqlite3.connect(":memory:")
cursor = db.cursor()

cursor.execute(query)
db.commit()

# get table name
cursor.execute("select name from sqlite_master where type = 'table'")
table_name = cursor.fetchone()[0]
print(table_name)

# get primary key columns
cursor.execute("PRAGMA table_info(%s);" % table_name)
pk_columns = [row[1] for row in cursor.fetchall()[::-1]]
print(pk_columns)

Prints:

dhcpr_dhcprelayinterface
['ifName', 'vrId']

Thanks for the good explanation. Can you please provide regrex expression if u have idea. — rishi narian, Feb 25 '16 at 03:40

Akram Parvez · Answer 3 · 2016-02-25T03:45:56.520

1

You can use this to just get the table name and primary key.

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

Outputs:

In [1]: a = """CREATE TABLE 'dhcpr_dhcprelayinterface'  (
...:         'vrId' integer default 0,
...:         'ifName' string ,
...:         PRIMARY KEY(ifName,vrId),
...:         FOREIGN KEY (vrId) REFERENCES 'vr_vr'(vrId) ON DELETE CASCADE ON
UPDATE CASCADE);"""
In [2]: begin = re.compile(r"CREATE TABLE[ ']*([^ ']+)[ ']*[(][^/;]+(PRIMARY KEY.*),[^/;]+;$", re.IGNORECASE)
In [3]: begin.findall(a)
Out[3]: [('dhcpr_dhcprelayinterface', 'PRIMARY KEY(ifName,vrId)')]

edited Feb 25 '16 at 03:45

answered Feb 25 '16 at 03:25

Akram Parvez

451
3
8

I tried with the above expression it is not working. – rishi narian Feb 25 '16 at 03:38
Thanks. Is it possible to print the data, using begin.search(a) instead of findall() – rishi narian Feb 25 '16 at 04:02
Yes, use ````match = begin.search(a)```` and this returns a match object and you get your data using ````match.groups()```` – Akram Parvez Feb 25 '16 at 05:59
I have the variable a that you are using as a really big string of SQL statements. I used the rest of the code exactly how it is but didn't work for me at all. I got nothing returned. – RB17 Aug 29 '19 at 18:07

score 1 · Accepted Answer · answered Feb 25 '16 at 03:45

1

The following was tested using python2.7:

>>> table_string = """
... CREATE TABLE 'dhcpr_dhcprelayinterface'  (
...         'vrId' integer default 0,
...         'ifName' string ,
...         PRIMARY KEY(ifName,vrId),
...         FOREIGN KEY (vrId) REFERENCES 'vr_vr'(vrId) ON DELETE CASCADE ON UPDATE CASCAD
E);"""
>>> p = r'CREATE TABLE\s+\'([^\']+)[\s\S]+PRIMARY KEY\(([^,]+),([^\)]+)\)'
>>> re.findall(p,table_string)
[('dhcpr_dhcprelayinterface', 'ifName', 'vrId')]

The explanation can be found here.

answered Feb 25 '16 at 03:45

Quinn

4,394
2
21
19

Thanks, is it possible to get the output using re.search() operation – rishi narian Feb 25 '16 at 04:02
@rishinarian: Yes. `re.search` will scan through string looking for a match to the pattern, returning a match object, or None if no match was found. So, you can do `m = re.search(p,table_string)`, then `print [m.group(i+1) for i in range(3)]`. The output is: `['dhcpr_dhcprelayinterface', 'ifName', 'vrId']`. – Quinn Feb 25 '16 at 04:46

Need regex expression to get only Tably name and primary key in python

TABLE:

4 Answers4

Linked