CODEWITHSUNDEEP
pythonmysql
David542
David542

Reputation: 110143

Suggested formatting for SQL SELECT return

I am doing the following pattern in my python code:

self.cursor1.execute(
     '''SELECT title, availed_title, episode_number,
               platform_id_series, platform_id_season, 
               platform_id_episode, season_number, url,
               provider_id, country, currency_code, est_hd_offer,                   
               est_sd_offer, vod_hd_offer, vod_sd_offer
     FROM main_googlecatalogtmp WHERE asset_type="episode"'''
)

item = cursor.fetchone()
title, availed_title, platform_id_series,
platform_id_season, platform_id_episode,
season_number, url, provider_id, country,
currency_code, est_hd_offer, est_sd_offer,
vod_hd_offer, vod_sd_offer = item

What would be a 'cleaner' way to define all those variables?

Upvotes: 0

Views: 55

Answers (1)

abarnert
abarnert

Reputation: 365707

The "cleanest" solution might be to use something like SQLAlchemy Core, or even a full ORM, to wrap things up. Then you'd be writing code that just queries for matching objects, and the objects have a title attribute, an episode_number attribute, etc. But that may be more heavy-duty than you want, or maybe some of your logic doesn't fit the OODB model very well, or maybe that's just too much code to change at this point.

But you can get a step in that direction just by using a namedtuple-style cursor or row factory.

Assuming you're using MySQL's own Connector/Python as the interface, you can specify the cursor type explicitly (see the list of all cursor subclasses), or specify flags and let it choose the cursor type that matches those flags. For example:

self.cursor1 = db.cursor(named_tuple=True)
# ...
self.cursor1.execute(
     '''SELECT title, availed_title, episode_number,
               platform_id_series, platform_id_season, 
               platform_id_episode, season_number, url,
               provider_id, country, currency_code, est_hd_offer,                   
               est_sd_offer, vod_hd_offer, vod_sd_offer
     FROM main_googlecatalogtmp WHERE asset_type="episode"'''
)
item = cursor.fetchone()
print('My title is {}'.format(item.title))

Depending on your use case, a dict might fit better than a namedtuple. For example:

self.cursor1 = db.cursor(dictionary=True)
# ...
self.cursor1.execute(
     '''SELECT title, availed_title, episode_number,
               platform_id_series, platform_id_season, 
               platform_id_episode, season_number, url,
               provider_id, country, currency_code, est_hd_offer,                   
               est_sd_offer, vod_hd_offer, vod_sd_offer
     FROM main_googlecatalogtmp WHERE asset_type="episode"'''
)
item = cursor.fetchone()
print('My title is {title} and my url is {url}'.format(**item))

For performance tradeoffs, the same fully-buffered/rowset-buffered/row-buffered options that control how many rows are read and type-converted at a time also control how many rows are wrapped up in a dict or namedtuple. It may be a little faster to buffer as much as possible, but of course it costs memory; when you really need to fine-tune things, the best answer may be to break a result into multiple result sets of exactly the right size and fully buffer each of them, but normally that isn't worth doing.

Upvotes: 2

Related Questions