How to handle nulls when using wxPython with a database

wxPython Users
1

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Imagine that txt_bar is an instance of wx.TextCtrl.

Here is an example code snippet:

···

-------------------------
bar = self.txt_bar.GetValue()
fred = ...

self.cur.execute("update foo set (bar, fred) = (%s, %s) where...")

-------------------------
(Note: I am putting the value into an intermediate variable so as to
avoid excessively long SQL statements)

If there is text in the control, all well and good. But if the user
elects to leave the control empty (which in this case is a valid option),
then the code as written will insert a zero-length string into the
database column. That is *not* what I am looking for; I want to put NULL
instead.

What I am unsure about is the most elegant way to achieve this. Is there
a way to do it without a series of "if" statements?

2

Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Imagine that txt_bar is an instance of wx.TextCtrl.

Here is an example code snippet:

-------------------------
bar = self.txt_bar.GetValue()
fred = ...

self.cur.execute("update foo set (bar, fred) = (%s, %s) where...")
-------------------------
(Note: I am putting the value into an intermediate variable so as to
avoid excessively long SQL statements)

Are you doing your own string substitutions, or are you letting your
database adapter do the substitutions? That's a trick question, you
should ALWAYS let your database adapter do the substitutions, to avoid
SQL injection attacks. Since you didn't quote the %s there, I will
assume you are doing so.

If you do so, then it depends on which adapter you are using. psycopg,
for example, will convert the Python None constant to an SQL NULL. So,
you could say something like:

    bar = self.txt_bar.GetValue() or None
    fred = self.txt_fred.GetValue() or None
    self.cur.execute("UPDATE foo SET bar=?, fred=? WHERE key=?;", (bar,
fred, key))

That is *not* what I am looking for; I want to put NULL instead.

Really? That's somewhat unusual. NULLs tend to be a thorn in the side
of database developers, because NULL behaves badly in comparisons.
Personally, I prefer to write zero-length strings.

···

--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

3

Yes, I'm using psycopg2. If that converts Python None to NULL, it's just
the ticket! The "or None" is the magic key for me.

Many thanks for the guidance.

By the way, I'm coming to this as a DBA getting into development - that's
probably why I prefer NULLs to empty strings! I do know how to handle the
comparison issues, of course.

Thanks again!

WH

···

On Fri, 20 Jan 2012 10:47:44 -0800, Tim Roberts wrote:

Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Imagine that txt_bar is an instance of wx.TextCtrl.

Here is an example code snippet:

-------------------------
bar = self.txt_bar.GetValue()
fred = ...

self.cur.execute("update foo set (bar, fred) = (%s, %s) where...")
-------------------------
(Note: I am putting the value into an intermediate variable so as to
avoid excessively long SQL statements)

Are you doing your own string substitutions, or are you letting your
database adapter do the substitutions? That's a trick question, you
should ALWAYS let your database adapter do the substitutions, to avoid
SQL injection attacks. Since you didn't quote the %s there, I will
assume you are doing so.

If you do so, then it depends on which adapter you are using. psycopg,
for example, will convert the Python None constant to an SQL NULL. So,
you could say something like:

    bar = self.txt_bar.GetValue() or None fred =
    self.txt_fred.GetValue() or None self.cur.execute("UPDATE foo SET
    bar=?, fred=? WHERE key=?;", (bar,
fred, key))

That is *not* what I am looking for; I want to put NULL instead.

Really? That's somewhat unusual. NULLs tend to be a thorn in the side
of database developers, because NULL behaves badly in comparisons.
Personally, I prefer to write zero-length strings.

4

Someone really ought to recommend this:

   http://www.sqlalchemy.org/

Michael

···

On 2012-01-20 12:33 PM, Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

5

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

···

On Fri, 20 Jan 2012 16:15:51 -0600, Michael Hipp wrote:

On 2012-01-20 12:33 PM, Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Someone really ought to recommend this:

   http://www.sqlalchemy.org/

6

Nothing :slight_smile:

···

On Sat, 21 Jan 2012 00:27:53 +0000 (UTC) Walter Hurry <walterhurry@lavabit.com> wrote:

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

--
Save Soviet Jewry -- Win Valuable Prizes!!!!

7

IMO the

···

On 1/20/12 4:27 PM, Walter Hurry wrote:

On Fri, 20 Jan 2012 16:15:51 -0600, Michael Hipp wrote:

On 2012-01-20 12:33 PM, Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Someone really ought to recommend this:

    http://www.sqlalchemy.org/

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

--
Robin Dunn
Software Craftsman

8

If "another abstraction layer" is an unequivocally bad thing, then yes, ignore it. You do realize that Python is just another abstraction layer on your CPU, right?

SQLA makes a lot of things really easy and allows your thinking about database objects and operations to take place at a much higher level. Mostly that's a good thing, IMHO.

Take a look at some of the tutorials and see if anything catches your imagination. I like it and evidently so do a lot of others as it is widely used.

Cheers.

Michael

···

On 2012-01-20 6:27 PM, Walter Hurry wrote:

    http://www.sqlalchemy.org/

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

9

Sorry, I realized I was replying to the wrong message but then clicked send instead of delete. But since I'm here I may as well comment...

I haven't really used SQLAlchemy enough myself yet to have a good solid opinion of it, but I think that one of the nicest things about it and similar tools is that it lets you think about your application from a different perspective. You can more easily be thinking about things from an object oriented perspective instead of tables/rows/columns etc. The time for that type of perspective is when defining the data dictionary, setting up constraints or indexes, etc., but when applications are developed from that perspective my experience is that they are usually a jumbled and fragile mess. The ORM in SQLAlchemy does a good job of bridging the gap between the 2 paradigms, and gets out of your way when you need it to. The other parts of SQLAlchemy help you do in few lines of Python code what would usually take many more lines of SQL to do, so that is a very nice thing for most developers, but if your comfort level is more on the SQL side then it's certainly possible to use it that way too.

···

On 1/20/12 4:39 PM, Robin Dunn wrote:

On 1/20/12 4:27 PM, Walter Hurry wrote:

On Fri, 20 Jan 2012 16:15:51 -0600, Michael Hipp wrote:

On 2012-01-20 12:33 PM, Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Someone really ought to recommend this:

http://www.sqlalchemy.org/

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

IMO the

--
Robin Dunn
Software Craftsman

10

The other thing SQLAlchemy offers you is db engine freedom, currently you use PostgreSQL, what if you want to switch to Sqlite or Firebird or ..., with SA this is in most cases just a change of your connection string.

If you like to see SA used with wxPython you might want to have a look at a little sample application Mike and I put together.

https://bitbucket.org/driscollis/medialocker

Werner

···

On 21/01/2012 01:27, Walter Hurry wrote:

On Fri, 20 Jan 2012 16:15:51 -0600, Michael Hipp wrote:

On 2012-01-20 12:33 PM, Walter Hurry wrote:

I am just starting to use wxPython with a relational database
(PostgreSQL), and am unsure of the best way to handle nulls when
inserting/updating. Some advice would be appreciated.

Someone really ought to recommend this:

    http://www.sqlalchemy.org/

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

11

Walter Hurry wrote:

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

Guilt, as far as I can tell.

I've tried to get the SQLAlchemy religion. I really have. I'm sure it
would help me eventually, but as someone who is comfortable building SQL
queries, it has always seemed to be an unnecessary extra step. I would
do things backwards: I would start from the SQL I want, and then think
"now how do I convert that in SQLAlchemy?" And since I do my testing in
a psql command line, where I need real SQL, I would constantly be
converting back and forth.

Some day I will be converted. Maybe.

···

--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

12

Hah, you a have struck a chord with the "doing it backwards" remark. I am
reminded of a commercial reporting tool called Business Objects (now
owned by SAP I believe), which I encountered many years ago.

The developers managed to create some "Universes" with truly dreadful
performance. The SQL generated was horrendous.

As an Oracle DBA I had to attend a Business Objects training course so as
to help the developers design BO "Universes" which would have half a
chance of producing reports before the business people gave up and went
home for the day.

Not that I'm accusing SQLAlchemy of any of that, of course. It's just
that I don't see a need for it in our case (at least not yet).

···

On Mon, 23 Jan 2012 11:24:30 -0800, Tim Roberts wrote:

Walter Hurry wrote:

Thanks, but I'm a bit confused. What does SQLAlchemy actually bring to
the party that psycopg2 doesn't? I'm very comfortable with SQL and
relational databases, and if I have understood the SQLAlchemy stuff
correctly, all it does is introduce another abstraction layer.

There must be something I am missing. What is it?

Guilt, as far as I can tell.

I've tried to get the SQLAlchemy religion. I really have. I'm sure it
would help me eventually, but as someone who is comfortable building SQL
queries, it has always seemed to be an unnecessary extra step. I would
do things backwards: I would start from the SQL I want, and then think
"now how do I convert that in SQLAlchemy?" And since I do my testing in
a psql command line, where I need real SQL, I would constantly be
converting back and forth.

Some day I will be converted. Maybe.

13

Well, I don’t have any problem composing SQL queries, but I’ve never really enjoyed doing so. I use sqlalchemy because it lets me do everything in python. I can define my database structure in python, create the tables in python, and run all queries in python. I don’t have to worry about the differences in implementation details between MySQL, posgreSQL, and sqlite, because sqlalchemy takes care of all that. If I ever really need to run a SQL query, I can ask sqlalchemy for a connection to the database, and I’ll be talking directly to the python dbapi.

Before I started using sqlalchemy, I used to spend a lot of effort writing code to generate queries, especially inserts, for tables with many columns. With sqlalchemy, that is effortless. Personally, I only use the low level part of sqlalchemy, which wraps dbapi, and provides the metadata abstraction. The ORM looks very nice, but I prefer to roll my own for each project. I find this has too much dependence on the specific details of the application, and it isn’t very hard to create a custom mapping for each database, which takes into account the oddities of the actual business objects, rather than trying to figure out how to use someone else’s ORM to handle the specific issues my project entails.

···

–
Best Regards,
Michael Moriarity

14

I would start from the SQL I want, and then think
"now how do I convert that in SQLAlchemy?" And since I do my testing in
a psql command line, where I need real SQL, I would constantly be
converting back and forth.

ditto

Some day I will be converted. Maybe.

I won't, for my clients only perform readings;
other operations are handled by stored procedures.

JY

···

On Mon, 23 Jan 2012 11:24:30 -0800 Tim Roberts <timr@probo.com> wrote:
--
QOTD:
"Unlucky? If I bought a pumpkin farm, they'd cancel Halloween."