Datapipe Weekly #17

Hello friend! This is a newsletter for builders.

What do you like to build?

I hope the ideas in this week’s newsletter can help you get it done.

In this weeks newsletter

  • 💻 Trick: Running raw SQL and reading data with Pandas and Sqlalchemy

  • 📜 Quote of the week


Trick: Running raw SQL and reading data with Pandas and Sqlalchemy

My database of choice has always been postgres. In the beginning I used psycopg2 to connect, run queries and pull data into python.

It worked, it was fine, but there were some little issues that I grew tired of

  • Dealing with cursors (in addition to connections)

  • No support for DB URLs

  • Can be difficult to install psycopg2

OK so not a very long list of gripes- and I’m not even sure that 3rd one is valid to be fair.

Anyway… here’s how I prefer to do it now using Sqlalchemy

import sqlalchemy
import pandas as pd
# os.environ["DB_URL"] = "postgresql://USER:PASS@HOST:PORT/DATABASE"
engine = sqlalchemy.create_engine(os.environ["DB_URL"])
# IF YOU NEED TO COMMIT USE BEGIN / COMMIT
sql_cmd = """
begin;
-- SQL GOES HERE
commit;
"""
with engine.connect() as conn:
result = conn.execute(sql_cmd)
# IF YOU ARE READING DATA
sql_cmd = "select * from table"
df = pd.read_sql(sql_cmd, engine)
engine.dispose()
  • In line 5, I read my DB_URL from an environment variable. I’m on mac, so I set these in my ~/.bash_profile so they are always available for me.

  • In line 13 I connect to my database and then execute the sql query. It’s important to use begin; and commit; otherwise your database won’t be affected.

  • In line 19 I read the result of some query using pandas. This two-liner (which could even be a one-liner) is also sets the column names for you.

  • In line 21 I close the sqlalchemy engine. This would remove any existing connections which had not been closed. In our case there isn’t any - so this line actually has no purpose for us. Hehe.


Quote of the week

“What I regret most in my life are failures of kindness”
- George Saunders

Last week I sat down to meditate for 15 minutes. Half way through my phone rang and I took the call. It was a telemarketer from New York trying to sell me on some investing service. They didn’t even know my name.

The lady on the other end sounded nice and was polite, but that didn’t stop me from commanding to “remove my number from their database and never call me again” before immediately hanging up on her.

After that I couldn’t get back to my meditation. I felt too guilty for being rude to that lady. She has a tough job - it’s not her fault that my phone number appeared on her list that day.

No different from myself, she’s just another consciousness navigating her reality the best she can. I had the chance to make that easier on her and I failed.

I didn’t lose my meditation that day because of her, but because of a lack of compassion. When seeking to be happy, I’ve found, a little can go a long way.

-Alex


Thank you for reading Datapipe 👋


Subscribe the Datapipe weekly newsletter ⬇️