Forum Archive

I had tried using sqlalchemy before but found it hard just to get started. I watched a YouTube video today link that made it a lot easier just to get started. Below I posted some code to write the pytz time zone strings to a SQLite table. Then query to see if a particular tz string is in the table. Not saying this is complete or the best way to do it. But if you have had problems getting going as I have in the past, this may help to get past that feeling. The video basically follows the example in the documentation, but a little easy to follow in my mind.

Anyway, it seems like if you want to use a database ORM these days, sqlalchemy looks like it is at the top of the list for many developers. Just from what I read and listen to.
Also sqlalchemy is shipped with Pythonista

import sqlalchemy
import pytz

print('sqlalchemy Version {}'.format(sqlalchemy.__version__ ))

cnn_str = 'sqlite:///:memory:'

# uncomment the below to write a file instead of memory
#cnn_str = 'sqlite:///pytz_strings.db'

from sqlalchemy import create_engine

# below can set echo to True, but a lot of console output
engine = create_engine(cnn_str, echo=False)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

class TzNames(Base):
    __tablename__ = 'tz'

    id = Column(Integer, primary_key=True)
    tz_name = Column(String)

    def __repr__(self):
        return "<TzNames(tz_name='%s'" % (self.tz_name)

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()


for tz in pytz.all_timezones:
    tz_names = TzNames(tz_name=tz)
    session.add(tz_names)
session.commit()

query_str = 'US/Pacific'
if session.query(TzNames).filter(TzNames.tz_name == query_str).first():
    print('{}, was found in database...'.format(query_str))
else:
    print('{}, was not found in database...'.format(query_str))

print('Number of tz records = {}'.format(session.query(TzNames).count()))