3

I am trying to write a string to a file. This file will be a query to Microsoft's SQL server, so it must follow a specific format. This is where my problem comes in.

Assuming the rest of the code is correct, I have my write method like so:

file.write("INSERT INTO SAMPLE_TABLE (int_value, string_value, comment)\n"
           "VALUES (%d, '%s', '%s')\n\n" 
           % (row["int_value"], row["string_value"], row["comment"]))

As you can see, I need to put quotations around %s because that is the syntax of the query. I should also mention that I am developing a GUI. The user has the option to enter in a comment. If the user does not enter anything, row["comment"] will be None. However, because that I have quotations around %s, it will write 'None', which will be a string in the database as apposed to None, which translates into NULL in the database, which is what I want.

I could do something like this:

if row["comment"] is None:
    file.write("INSERT INTO SAMPLE_TABLE (int_value, string_value, comment)\n"
               "VALUES (%d, '%s', %s)\n\n" 
               % (row["int_value"], row["string_value"], row["comment"]))
else:
    file.write("INSERT INTO SAMPLE_TABLE (int_value, string_value, comment)\n"
           "VALUES (%d, '%s', '%s')\n\n" 
           % (row["int_value"], row["string_value"], row["comment"]))

but that's two lines of code. And what if later on I realize that more than one value could be None? I'll have to check every single case! I need to make this dynamic.

Any help is appreciated.

Don D.
  • 109
  • 9

6 Answers6

3

How about this?

comment_str = "None" if row["comment"] is None else "'{}'".format(row["comment"])
file.write("INSERT INTO SAMPLE_TABLE (int_value, string_value, comment)\n"
           "VALUES (%d, '%s', %s)\n\n" 
           % (row["int_value"], row["string_value"], comment_str))
Bharel
  • 23,672
  • 5
  • 40
  • 80
2

What about not adding the quotes, and later substituting it with a comment that has the quotes (double substitution, but can be done in a single line)?

row = {"comment": "abc"}
comment = row.get("comment") # Note, bracket lookup would have thrown a KeyError, not returning None
values = "%d, '%s', %s" % (1, "string_value", "'%s'" % comment if comment is not None else None)
print values

Which will either print 1, 'string_value', 'abc' or 1, 'string_value', None

Then just substitute values using VALUES = (%s) % values

Note that you actually don't even need to separate out VALUES into another substitution if you just keep

comment = row.get("comment")
# In your string:
VALUES = (%s) % ("%d, '%s', %s" % (row["int_value"], row["string_value"], "'%s'" % comment if comment is not None else None))

Here's a basic example:

>>> comment = None
>>> 'Values = (%s)' % ("'%s'" % comment if comment is not None else None)
'Values = (None)'
>>> comment = "test"
>>> 'Values = (%s)' % ("'%s'" % comment if comment is not None else None)
"Values = ('test')"
>>> comment = "" # For empty comments, thanks to @Bharel for pointing this out
>>> 'Values = (%s)' % ("'%s'" % comment if comment is not None else None)
"Values = ('')"
Bahrom
  • 4,752
  • 32
  • 41
  • 1
    In case of an empty comment, this will result in None. – Bharel Mar 15 '16 at 20:59
  • @Bharel, and that's what we want right? Because if we get a `'None'` it gets treated as a string in the db (note, no quotes in `Values = (%s)`), but they get added if there is a comment). Edit oh nevermind, I see what you mean now. – Bahrom Mar 15 '16 at 21:07
  • 1
    @U Not entirely. We want empty comments to be empty, not `None`. – Bharel Mar 15 '16 at 21:12
  • @Bharel, I see what you mean now, that makes sense, see edited answer :) – Bahrom Mar 15 '16 at 21:12
2

You could use any python mysql module and its cursors option

cursor.mogrify(query, args)

if you are already using it. It will properly escape everything depending on type.

If you want to do it manually then you can do a simple

row['comment'] if row['comment'] is None else "'".join(['',row['comment'],''])

before the query

Ajay
  • 161
  • 11
  • In case of an empty comment, this will result in an error in the sql query, and it does not get the required result. – Bharel Mar 15 '16 at 20:59
  • this works but instead of the join you may do this: `"'{}'".format(row['comment'])` – Bharel Mar 15 '16 at 21:16
  • Sometimes join is faster. I know I know premature optimization, but still. http://stackoverflow.com/questions/3055477/how-slow-is-pythons-string-concatenation-vs-str-join – Ajay Mar 15 '16 at 21:21
  • join will not be faster in this case, it will actually be much much slower. – Bharel Mar 15 '16 at 22:00
0

Why not just insert

if row["comment"] is not None:
    row["comment"] = "'%s'" % row["comment"]

file.write(...

It gives you the custom behavior you want and doesn't require repeating any code

DaveBensonPhillips
  • 3,134
  • 1
  • 20
  • 32
  • Is there a reason you can't just state ``if row["comment"]:`` as your first line? This will evaluate to ``True`` if ``row["comment"]`` is anything but ``None``. – Igor Mar 15 '16 at 21:00
  • 1
    @Igor `if row` will also happen in case of an empty comment. Not good. Problem with this answer is that it edits the comment. – Bharel Mar 15 '16 at 21:01
  • @Igor as Bharel says, that will map the empty string to "None", which is not Specified behavior – DaveBensonPhillips Mar 15 '16 at 21:30
0

You can try with

file.write("INSERT INTO SAMPLE_TABLE (int_value, string_value, comment)\n"
       "VALUES (%d, '%s', '%s')\n\n" 
       % (row["int_value"], row["string_value"], row["comment"] or 'NULL'))
Mr. E
  • 2,070
  • 11
  • 23
0

I would use repr(row["comment"]) with %s. It gives you quotes around a string, but leaves the None literal alone.

>>> 'VALUES (%s)' % repr(None)
'VALUES (None)'
>>> 'VALUES (%s)' % repr("string")
"VALUES ('string')"
VPfB
  • 14,927
  • 6
  • 41
  • 75