Python - Regex, multiple match

Question

I have a file which conatains amongst others SQL-CREATE-TABLE-commands. I want to write all SQL-CREATE-TABLE-commands into a list (not implemented yet), each command in a seperate list entry.

My problem is, that the regular expression does only return the first match, but there should be more.

Source file:

abcd
something
CREATE TABLE schema.test1(attribute1 DECIMAL(28, 7)  NULL , 
ATTRIBUTE2 DECIMAL(28, 7)  KEY  NOT NULL , 
ATTRIBUTE3 DECIMAL(28, 7)  NOT NULL , 
SET("db_alias_name" = 'TEST')
;

efgh
something else
CREATE TABLE schema.test2(attribute1 DECIMAL(28, 7)  NULL , 
ATTRIBUTE2 DECIMAL(28, 7)  KEY  NOT NULL , 
ATTRIBUTE3 DECIMAL(28, 7)  NOT NULL , 
SET("db_alias_name" = 'TEST')
;

something else
CREATE TABLE schema.test3(attribute1 DECIMAL(28, 7)  NULL , 
ATTRIBUTE2 DECIMAL(28, 7)  KEY  NOT NULL , 
ATTRIBUTE3 DECIMAL(28, 7)  NOT NULL , 
SET("db_alias_name" = 'TEST')
;
something else
12346
higkl

My script only returns the first match:

CREATE TABLE schema.test1(attribute1 DECIMAL(28, 7)  NULL , 
ATTRIBUTE2 DECIMAL(28, 7)  KEY  NOT NULL , 
ATTRIBUTE3 DECIMAL(28, 7)  NOT NULL , 
SET("db_alias_name" = 'TEST')

Script:

# -*- coding: utf-8 -*-
import os
import re

create_table_parts = []

atlfile = 'example.txt'
data = ''

def read_file(afile):
    with open(afile) as atl:
        text = atl.read()
        return text

data = read_file(atlfile)
data_utf8 = unicode(data, "utf-8")

round1 = re.search(r"(CREATE\sTABLE).+?(?=;)", data_utf8, re.MULTILINE|re.DOTALL)
print round1.group()

Could you maybe tell me, what's wrong here?

You might benefit from using a parser instead of relying on regular expressions. github.com/andialbrecht/sqlparse — Joel Cornett
– Joel Cornett, Commented May 21, 2014 at 13:59

Mark · Accepted Answer · 2014-05-21 13:28:20Z

2

You'd be better off using finditer because it returns a match object like search:

someIter = re.finditer(r"(CREATE\sTABLE).+?(?=;)", data_utf8, re.MULTILINE|re.DOTALL)
for mObj in someIter:
    # process mObj

answered May 21, 2014 at 13:28

Mark

109k20 gold badges180 silver badges238 bronze badges

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

Comments

Oscar Olsson · Accepted Answer · 2014-05-21 12:44:36Z

1

You could use findall instead, see https://docs.python.org/2/library/re.html#re.findall

answered May 21, 2014 at 12:44

Oscar Olsson

212 bronze badges

2 Comments

royskatt Over a year ago

For some reason the postive lookahead isn't working with re.findall , it returns only the first part of the regex: print re.findall(r"(CREATE\sTABLE).+?(?=;)", data_utf8, re.MULTILINE|re.DOTALL) Returns: [u'CREATE TABLE', u'CREATE TABLE', u'CREATE TABLE']

Seb D. Over a year ago

Just use this regex instead 'CREATE\sTABLE.+?(?=;)'

royskatt · Accepted Answer · 2014-05-21 13:54:59Z

0

Thanks to Mark's hint, below now a working example solution:

# -*- coding: utf-8 -*-
import os
import re

create_table_parts = []
atlfile = 'example.txt'
data = ''

def read_file(afile):
    with open(afile) as atl:
        text = atl.read()
        return text

data = read_file(atlfile)
data_utf8 = unicode(data, "utf-8")


def round1_get_CT(text):
    match_list = []
    someIter = re.finditer(r"(CREATE\sTABLE).+?(?=;)", text, re.MULTILINE|re.DOTALL)
    for mObj in someIter:
        #print mObj.group()
        match_list.append(mObj.group())
    return match_list

create_table_parts = round1_get_CT(data_utf8)

print "\n".join(create_table_parts)

answered May 21, 2014 at 13:54

royskatt

1,2103 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

Python - Regex, multiple match

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related