search by tags

for the user

adventures into the land of the command line

python and databases

i wanted to write some python code that would be able to read and write from a database. right at the beginning i had no idea what to do, but then after much trial and error, i learnt to make a few python applications that work with MySQL, Postgres & Redis (so far). these are the things i learnt on the way:

let’s assume you know what kind of database you want to use already, and what you want your data model to look like. what you are really concerned with is HOW to get it to work.

let’s also assume you have installed and set up the database of your choice and you know how to use it.

the next thing you need to do, is install a specific kind of python library, known as a database driver which utilise python’s DB-API specified in Python PEP 249. here are some examples:

MySQL -> MySQL-python

$ pip install MySQL-python

Postgres -> psycopg2

$ pip install psycopg2

Redis -> redis-py

$ pip install redis

In your code, you can utilise them like this:

MySQL

from mysqlcreds import mysql_user, mysql_password, mysql_db
import MySQLdb as mdb

con = mdb.connect('localhost', mysql_user, mysql_password, mysql_db)

with con:
   cur = con.cursor()
   cur.execute("SELECT FROM mytable WHERE mycolumn LIKE '%BLAH%'")
   myvar = cur.fetchall()
con.close()

Postgres

from pgcreds import pgsql_dbuser, pgsql_dbpassword, pgsql_dbname
import psycopg2

con = psycopg2.connect(database=pgsql_db1name, user=pgsql_dbuser, password=pgsql_dbpassword, host='localhost')

with con:
   cur = con.cursor()
   cur.execute( """INSERT INTO mydb.mytable(\
      mycolumn) \
      VALUES(%s)""", \
      (somevalue) )
   con.commit()

Redis

import redis

redis_db = redis.Redis(host='127.0.0.1', port=6379)

a_key = 'blahblahblah'
somevalue = 'wabadubadubdub'
anothervalue = 'floob'

mapping = {'somekey':somevalue,'anotherkey':anothervalue}
redis_db.hmset(a_key, mapping)

you might have noticed, with the MySQL & Postgres drivers, you are putting SQL statements directly into your python code. functionally this works fine, but then i learned about something called SQL injection. apparently it’s bad practice to code like this, if you are making an app that allows user input, as someone could put SQL into your data input fields that might have the effect of dropping a table or returning all the information in your database before deleting it all

so this is when i came across something called an ORM, or object relational mapper. they map SQL data objects to a python class, so that your code looks like python code and there are no SQL statements anywhere. even though you still need to have the relevant db driver installed, this adds a layer of abstraction between the end user and the database, removing the threat of SQL injection.

the most popular one for python seems to be SQLAlchemy, but when i checked it out it seemed to have a steep learning curve. i came across another one called peewee that i quite like. it works something like this

Postgres

from creds import pgsql_dbuser, pgsql_dbpassword, pgsql_dbname
from peewee import *
psql_db = PostgresqlDatabase(pgsql_dbname, user=pgsql_dbuser, password=pgsql_dbpassword, host='localhost')

class BaseModel(Model):
    # A base model that will use our Postgresql database
    class Meta:
        database = psql_db
        schema = 'myschema'

class Dbusers(BaseModel):
    uuid = UUIDField(primary_key=True, unique=True)
    email = CharField(max_length=40)

class Dbtransactions(BaseModel):
    transaction_id = DateTimeField(primary_key=True, unique=True)
    user_id = UUIDField()

psql_db.connect()
Dbusers.create(uuid='somevalue',email='someemail')
psql_db.close()

psql_db.connect()
Dbtransactions.select().where(Dbtransactions.user_id == user)
psql_db.close()

note in the above code, i haven’t imported psycopg2, but it is installed, i think peewee utilises it in the background

so, peewee. small but mighty.

i’m recently heavily into redis, cos its super awesome fun times, i’ll do a post about it soon. but there you have it, the same concepts apply to other brands of databases as well, SQL or noSQL, and there are heaps and heaps of drivers and ORMs out there, i just wrote about the ones i’ve had experience with. go nuts!