Databases in Python

This post is about manipulating SQL databases.

Why a database instead of a text file? I have been recently confronted with the problem of storing information like multiline texts in a file. Is there a way for this to work with CSV files? Idk, but with databases this can be done easily as you will see below¹.

sqlite3 Module

import sqlite3
import pandas as pd

Creating a Database

Using Python’s sqlite3 module

conn=sqlite3.connect('example.db')
c=conn.cursor()

Now SQL commands can be performed, such as

c.execute('CREATE TABLE contacts (email1, email2, primarycontact)')

Inserting values

for i in range(5):
    c.execute("INSERT INTO contacts VALUES ('zyx@xyz','cba@abc','Dr. D')")

conn.commit()
conn.close()

great. Now let’s see if we can include a multiline text

s='''
this 
multi
line
string
'''
t=('wvu@uvw','gfe@gfe',s)
c.execute('INSERT INTO contacts VALUES (?,?,?)', t)
conn.commit()
conn.close()

Reading the contents of the Database

pd.read_sql('SELECT * FROM contacts',conn)

sqlalchemy Module

Creating a Database

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, create_engine
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('fullname', String),
)
addresses = Table('addresses', metadata,
  Column('id', Integer, primary_key=True),
  Column('user_id', None, ForeignKey('users.id')),
  Column('email_address', String, nullable=False)
 )

engine = create_engine('sqlite:///example2.db')
metadata.create_all(engine)
connection = engine.connect()

Reading the contents of the Database

Following SQLAlchemy’s basic tutorial

users=pd.read_sql_table('users', connection)

Inserting values

ins = users.insert()
# str(ins)
ins = users.insert().values(name='Jack', fullname='Jack Jones')
# ins.compile().params 
result = connection.execute(ins)

■

---

1. Now i know that I could have used characters such as ‘\n’ in pandas. ↩