1

I'm planning on making a function that when given a list and table name, it will insert the values of the list into the table. I know the maximum number of columns a table will have (for example sake let's say it's 4).

So I'd have

list = [col1, val1, col2, val2, col3, val3, col4, val4]
stmt = 'INSERT INTO {} ({}, {}, {}, {}) VALUES {}, {}, {}, {};'.format('myTable', list[0], list[1], list[2], list[3], list[4], list[5], list[6], list[7], list[8])
cur.execute(stmt)

but say for example another table only has two columns. Then if stmt = 'INSERT INTO {} ({}, {}, {}, {}) VALUES ({}, {}, {}, {});'.format('myTable', list[0], list[1], list[2], list[3], list[4], list[5], list[6], list[7], list[8]) were in a function the program would crash as list[7] and [list8] would be out of bounds! (see what I'm saying?)

In the example I have a simple list but currently the program has a list inside a list of the format [[val1, colName1], [val2, colName2],...,[valn, colNamen]] where vali is the value to go into coli and all of this will be on the same row in a table.

The problem is made more complicated because a value to be inserted may have experienced an error and be NoneType. Since you can't insert a null into Postgres, this would be an issue.

Celeritas
  • 14,489
  • 36
  • 113
  • 194
  • In case of a None/NULL shall we skip the column in the INSERT? – Pynchia Aug 08 '15 at 09:23
  • @Pynchia yes that's what normally is done, but if you're trying to make a function that just substitutes in the values to insert using `{}` how can it be skipped? For example in Python if the code looks like `cur.execute('INSERT INTO tableA col1 VALUES {}'.format(arg))` (where `arg` is passed as argument to function this is in) then how would you just "skip" it? – Celeritas Aug 08 '15 at 10:13
  • I have edited my answer. Have a look and tell me if it is acceptable now – Pynchia Aug 08 '15 at 12:42
  • All the code in this question is scary bad. See http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Kevin Aug 08 '15 at 13:00

2 Answers2

1

Here is my solution, which skips None values

def insert_stmt(table, l):
    vals = ['"'+str(el)+'"' for n, el in enumerate(l) if l[n] and n % 2]
    if not vals:
        return ""
    cols = [el for n, el in enumerate(l) if n < (len(l)-1) and l[n+1] \
                                            and not n % 2]
    stmt = 'INSERT INTO {} ({}) VALUES ({});'.format(table,
                                                   ', '.join(cols),
                                                   ', '.join(vals))
    return stmt

lst = ['col1', 1, 'col2', None, 'col3', 3, 'col4', None]
print insert_stmt('mytable', lst)

lst_none = ['col1', None, 'col2', None]
print insert_stmt('mytable', lst_none)

produces

INSERT INTO mytable (col1, col3) VALUES ("1", "3");
<empty string>

It assumes the column names are strings.

Pynchia
  • 10,996
  • 5
  • 34
  • 43
  • Thanks. I'm new to Python, where does `el` come from and what does it mean when something comes before a `for` loop? – Celeritas Aug 09 '15 at 09:05
  • please tell me if my solution solves the problem or not. If it does, please accept the answer (if it is the best/most suitable answer in your opinion, of course). – Pynchia Aug 09 '15 at 11:13
  • about the python syntax I used: those are called `list comprehensions`. Such statements build lists according to a given condition. Have a look at [this tutorial](http://www.secnetix.de/olli/Python/list_comprehensions.hawk). It's an interesting an long subject to be explained here in full. Search online/study a book for more resources about Python's iterators, generators comprehensions, etc. – Pynchia Aug 09 '15 at 11:20
  • Sorry for the delay, I've been busy and still am going through the test cases. – Celeritas Aug 09 '15 at 23:33
  • On line 3 you have your quotes inverted `'"'` needs to be `"'"` – Celeritas Aug 10 '15 at 01:17
  • The function doesn't handle the situation where all values are null, if this is the situation the empty string should be returned (or whatever signal for failure you see best). – Celeritas Aug 10 '15 at 01:17
  • OK. I thought you wanted help on how to handle the problem, not a production-ready solution. That said, now it handles the "all-None" case. As you can see, it's fairly easy to check. – Pynchia Aug 10 '15 at 09:49
  • @Celeritas the quotes work fine either way in python. I use the double quotes in the SQL statement, as Django does, for example. – Pynchia Aug 10 '15 at 09:51
  • What makes you say the quotes work fine either way? Single quotes and double quotes make a difference in Postgres. – Celeritas Aug 10 '15 at 16:16
  • Also `VALUES {}` must be `VALUES ({})` – Celeritas Aug 10 '15 at 21:51
  • I see, easy to fix. correct it in your question then, I copied the SQL from that – Pynchia Aug 10 '15 at 21:53
0

Here's an idea:

    a = ['col1', '1']
    b = ['col2', '2']
    c = [a, b]
    col_val = zip(c)
    stmt = 'INSERT INTO {}({}) VALUES {};'.format('myTable', 
                                                 ", ".join(col_val[0]),
                                                 ", ".join(col_val[1]))
    cur.execute(stmt)

Obviously you might need to tweak it. I'd recommend learning something like SQLAlchemy though if you plan on doing much SQL stuff. Makes like easier.

Dealing with NoneType? Just doing some checking before inserting..?

shark3y
  • 166
  • 6