FAQ
Hello,


I have a list of sql queries, some which are split across multiple list
elements e.x.
['drop table sample_table;', 'create table sample_test', '(col1 int);',
'select col1 from', ' sample_test;']


A semi-colon in the string value indicates the termination of a sql query.
So the expected out come is a conversion to a list of valid sql queries:
['drop table sample_table;', 'create table sample_test (col1 int);',
'select col1 from sample_test;']


Here is the code that does that:


sample = ['drop table sample_table;', 'create table sample_test', '(col1
int);', 'select col1 from', ' sample_test;']
pure_sqls = []
query_holder= ''
for each_line in sample:
     query_holder += each_line
     if query_holder.endswith(';'):
         pure_sqls.append(query_holder)
         query_holder = ''




Is there a way to do this by eliminating explicit creation of new
list(pure_sqls) and a temporary variable(query_holder)? Using list
comprehension? Though I don't want to put the shorter version in
production(if it is difficult to understand), I am looking if this can be
done with list comprehension since I am trying to learn list comprehension
by using it in such scenarios.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20131212/cb4a5b32/attachment.html>

Search Discussions

  • Chris Angelico at Dec 12, 2013 at 9:46 pm

    On Fri, Dec 13, 2013 at 7:40 AM, Shyam Parimal Katti wrote:
    A semi-colon in the string value indicates the termination of a sql query.
    So the expected out come is a conversion to a list of valid sql queries:
    ['drop table sample_table;', 'create table sample_test (col1 int);', 'select
    col1 from sample_test;']

    Hmm. Just to clarify, does a semicolon _anywhere_ in the string
    terminate the query? If so, you have a problem of parsing, and the
    best thing to do is to enable multi-query processing on your database
    connection and send them all across. (You'd have to skip any
    semicolons inside quoted strings, for instance.) Your current code
    depends on the semi being at the end of one of the strings, which is a
    much safer proposition.


    If you really mean to split it anywhere, the easiest is to simply join
    the whole lot and then split on the semi:


    sample = ['drop table sample_table;', 'create table sample_test',
    '(col1 int);', 'select col1 from', ' sample_test;']
    pure_sqls = ''.join(sample).split(';')[:-1]


    Note that the last element from the split is NOT a valid query. If
    all's well, it should be an empty string (as it will be in this
    sample), but if it's not empty, it's a partial query. I don't know
    what you want to do with those; your code above will simply ignore
    them, so I've done the same here, applying the "trim off the last
    element" smiley operator [:-1] before assigning to pure_sqls.


    Parenthesis: I just asked the members of Threshold RPG what they
    thought [:-1] meant. 15 seconds after I asked, three responses came in
    almost simultaneously.


    Zeala: pothead smoking a roach
    Claudas: fallen jaw?
    Tharl: constipated, but happy.


    I don't know what that means for the OP's code. Somehow it doesn't
    seem a good omen. End parenthesis.


    For what you're doing, a list comp isn't really appropriate. Broadly,
    a list comp should be creating zero or one elements from each element
    of the source list; what you're trying to do here is stitching things
    together, which requires state. You can't do that with a list comp.
    The best way is probably what you already have, but if you'd like it
    to be shorter, you need simply invent a split marker that can't
    possibly exist in your queries, and use that. Let's suppose "\0" can't
    ever occur (that's likely, given that you're working with SQL).


    sample = ['drop table sample_table;', 'create table sample_test',
    '(col1 int);', 'select col1 from', ' sample_test;']
    pure_sqls = [s.replace('\0','') for s in
    '\0'.join(sample+['']).split(';\0') if s!='']


    Assuming the exact sequence ";\0" never comes up in your text, this
    will work perfectly. You could change out the replace call to put a
    delimiter in, if that made sense:
    pure_sqls = [s.replace('\0',' ') for s in
    '\0'.join(sample+['']).split(';\0') if s!='']


    This is also a reasonable example of a filtered list comp, as it'll
    suppress any blank entries in the result. Whether this is better or
    worse than trimming off the last unit depends on how you want to treat
    text after the last semicolon.


    ChrisA
  • Mark Lawrence at Dec 12, 2013 at 9:55 pm

    On 12/12/2013 20:40, Shyam Parimal Katti wrote:
    Hello,

    I have a list of sql queries, some which are split across multiple list
    elements e.x.
    ['drop table sample_table;', 'create table sample_test', '(col1 int);',
    'select col1 from', ' sample_test;']

    A semi-colon in the string value indicates the termination of a sql
    query. So the expected out come is a conversion to a list of valid sql
    queries:
    ['drop table sample_table;', 'create table sample_test (col1 int);',
    'select col1 from sample_test;']

    Here is the code that does that:

    sample = ['drop table sample_table;', 'create table sample_test', '(col1
    int);', 'select col1 from', ' sample_test;']
    pure_sqls = []
    query_holder= ''
    for each_line in sample:
    query_holder += each_line
    if query_holder.endswith(';'):
    pure_sqls.append(query_holder)
    query_holder = ''


    Is there a way to do this by eliminating explicit creation of new
    list(pure_sqls) and a temporary variable(query_holder)? Using list
    comprehension? Though I don't want to put the shorter version in
    production(if it is difficult to understand), I am looking if this can
    be done with list comprehension since I am trying to learn list
    comprehension by using it in such scenarios.

    I don't think this can be done with a list comprehension. As you appear
    to have a perfectly good piece of code, if it ain't broke, don't fix it
    :) Maybe change one line.


    if query_holder[-1] == ';':


    --
    My fellow Pythonistas, ask not what our language can do for you, ask
    what you can do for our language.


    Mark Lawrence
  • Chris Angelico at Dec 12, 2013 at 10:03 pm

    On Fri, Dec 13, 2013 at 7:40 AM, Shyam Parimal Katti wrote:
    sample = ['drop table sample_table;', 'create table sample_test', '(col1
    int);', 'select col1 from', ' sample_test;']
    pure_sqls = []
    query_holder= ''
    for each_line in sample:
    query_holder += each_line
    if query_holder.endswith(';'):
    pure_sqls.append(query_holder)
    query_holder = ''

    By the way, side point. It's generally considered good programming
    practice to use shorter names for short-lived variables and longer
    names for things that hang around. I'd spell this slightly
    differently:


    sample = ['drop table sample_table;', 'create table sample_test',
    '(col1 int);', 'select col1 from', ' sample_test;']
    pure_sqls = []
    cur = ''
    for line in sample:
         cur += line
         if cur.endswith(';'): # or line.endswith, or line[-1]==';'
             pure_sqls.append(cur)
             cur = ''


    The short one-token names go with the short usage; the longer name
    pure_sqls outlives them. Makes it easier to figure out what's
    important and what's intermediate. The name 'cur' there is debatable;
    I use it all over the place as a generic accumulator for the "current"
    whatever I'm working with, you might prefer to use "query" or even
    "q", take your pick.


    Makes no difference to the code, but might make it easier for someone
    to glance over your code and figure out what it's doing.


    ChrisA
  • Ben Finney at Dec 12, 2013 at 10:35 pm

    Shyam Parimal Katti <spk265@nyu.edu> writes:


    A semi-colon in the string value indicates the termination of a sql query.
    So the expected out come is a conversion to a list of valid sql queries:
    ['drop table sample_table;', 'create table sample_test (col1 int);',
    'select col1 from sample_test;']

    I presume these strings are split from lines of input. If so, you would
    be better advised to use an SQL parsing library in the first place
    <URL:https://pypi.python.org/pypi/sqlparse/>, to get distinct SQL
    statements from a text stream.


    --
      \ ?We must find our way to a time when faith, without evidence, |
       `\ disgraces anyone who would claim it.? ?Sam Harris, _The End of |
    _o__) Faith_, 2004 |
    Ben Finney
  • Peter Otten at Dec 13, 2013 at 9:43 am

    Shyam Parimal Katti wrote:


    Hello,

    I have a list of sql queries, some which are split across multiple list
    elements e.x.
    ['drop table sample_table;', 'create table sample_test', '(col1 int);',
    'select col1 from', ' sample_test;']

    A semi-colon in the string value indicates the termination of a sql
    query. So the expected out come is a conversion to a list of valid sql
    queries:
    ['drop table sample_table;', 'create table sample_test (col1 int);',
    'select col1 from sample_test;']

    Here is the code that does that:

    sample = ['drop table sample_table;', 'create table sample_test', '(col1
    int);', 'select col1 from', ' sample_test;']
    pure_sqls = []
    query_holder= ''
    for each_line in sample:
    query_holder += each_line
    if query_holder.endswith(';'):
    pure_sqls.append(query_holder)
    query_holder = ''


    Is there a way to do this by eliminating explicit creation of new
    list(pure_sqls) and a temporary variable(query_holder)? Using list
    comprehension? Though I don't want to put the shorter version in
    production(if it is difficult to understand), I am looking if this can be
    done with list comprehension since I am trying to learn list comprehension
    by using it in such scenarios.

    Yours is the sane approach, but it may be fun to try to understand the
    following evil hacks ;)

    [sql.replace("\0", " ") + ";" for sql in "\0".join(sample +
    [""]).split(";\0") if sql]
    ['drop table sample_table;', 'create table sample_test (col1 int);', 'select
    col1 from sample_test;']



    from itertools import groupby
    def key(x, group=[0]):
    ... try:
    ... return group[0]
    ... finally:
    ... group[0] += x.endswith(";")
    ...
    [" ".join(group) for _, group in groupby(sample, key)]
    ['drop table sample_table;', 'create table sample_test (col1 int);', 'select
    col1 from sample_test;']
  • Mark Lawrence at Dec 13, 2013 at 10:07 am

    On 13/12/2013 09:43, Peter Otten wrote:
    Shyam Parimal Katti wrote:
    Hello,

    I have a list of sql queries, some which are split across multiple list
    elements e.x.
    ['drop table sample_table;', 'create table sample_test', '(col1 int);',
    'select col1 from', ' sample_test;']

    A semi-colon in the string value indicates the termination of a sql
    query. So the expected out come is a conversion to a list of valid sql
    queries:
    ['drop table sample_table;', 'create table sample_test (col1 int);',
    'select col1 from sample_test;']

    Here is the code that does that:

    sample = ['drop table sample_table;', 'create table sample_test', '(col1
    int);', 'select col1 from', ' sample_test;']
    pure_sqls = []
    query_holder= ''
    for each_line in sample:
    query_holder += each_line
    if query_holder.endswith(';'):
    pure_sqls.append(query_holder)
    query_holder = ''


    Is there a way to do this by eliminating explicit creation of new
    list(pure_sqls) and a temporary variable(query_holder)? Using list
    comprehension? Though I don't want to put the shorter version in
    production(if it is difficult to understand), I am looking if this can be
    done with list comprehension since I am trying to learn list comprehension
    by using it in such scenarios.
    Yours is the sane approach, but it may be fun to try to understand the
    following evil hacks ;)
    [sql.replace("\0", " ") + ";" for sql in "\0".join(sample +
    [""]).split(";\0") if sql]
    ['drop table sample_table;', 'create table sample_test (col1 int);', 'select
    col1 from sample_test;']

    from itertools import groupby
    def key(x, group=[0]):
    ... try:
    ... return group[0]
    ... finally:
    ... group[0] += x.endswith(";")
    ...
    [" ".join(group) for _, group in groupby(sample, key)]
    ['drop table sample_table;', 'create table sample_test (col1 int);', 'select
    col1 from sample_test;']

    Evil? Bring back the death penalty for code like the above, that's what
    I say :)


    --
    My fellow Pythonistas, ask not what our language can do for you, ask
    what you can do for our language.


    Mark Lawrence

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedDec 12, '13 at 8:40p
activeDec 13, '13 at 10:07a
posts7
users5
websitepython.org

People

Translate

site design / logo © 2022 Grokbase