1

Am doing bulk inserts where some columns can be represented by empty string '' and I want to convert that empty string to a Postgresql DB Null value

If I was using psycopg2's copy_from, its easy and i'd have something like this:

copy_from(file_like_object, table, sep='|', null='', size=8192, columns=None)

The null='' would automatically convert the empty strings into DB Nulls.

However, right now am using SQlAlchemy. Does it have a similar magic parameter to its insert statement to convert empty strings to Null?

By insert statement I mean sqlalchemy core's .insert() as seen in example below

user_table = Table('user_table', metadata, autoload=True, autoload_with=engine)
sql = user_table.insert().values(sql_binder)
conn = engine.connect()
trans = conn.begin()
conn.execute(sql, db_users)
trans.commit()
conn.close()

I know I can iterate through each value and check if its an empty string and then pass Python's None but that is a lot of iterations and I want to avoid that.

lukik
  • 3,919
  • 6
  • 46
  • 89
  • What's wrong with a lot of iterations? Somewhere in the stack the list needs to be iterated, so it's not like you can improve on the O(n) run time. – univerio Mar 16 '16 at 06:51
  • Nothing wrong really. The situation is that each row to be inserted has got about 200 columns and each file `-csv-` to be imported has got about 100000 rows which will make 2 million iterations per file. I would love to avoid that if I can have a silver bullet like the copy_from function has... – lukik Mar 16 '16 at 07:10
  • My point is that even if SQLAlchemy supported it, it would have to iterate through and replace all empty strings with `None`s, just as you would do yourself. It won't make it magically faster just because the framework supports it. *However*, `copy_from` *is* usually faster, because it's a different mechanism, so you should use that instead if you care about speed. – univerio Mar 16 '16 at 17:39
  • My understanding is that sqlalchemy uses psycopg2 driver to connect to a postgresql database. We also know that `copy_from` is available in psycopg2. Question then is, does sqlalchemy expose this function? If it does, I'd like to use it. If it doesn't then tough luck and I will iterate. That is what the question is all about. So far I've not found a way to run a copy_from function from SQLAlchemy – lukik Mar 17 '16 at 11:46
  • In that case, you might want to take a look at [this](http://stackoverflow.com/questions/13125236/sqlalchemy-psycopg2-and-postgresql-copy). – univerio Mar 17 '16 at 18:20
  • At least that answers the availability of `copy_from` from SQLAlchemy. Thanks. I'll leave this question here in case someone can figure out how to do the `null` part i.e. convert empty strings to `db null` from SQLAlchemy even when not mimicking the `copy_from` functionality – lukik Mar 18 '16 at 02:53

0 Answers0