The Python Database API (DB-API) defines a standard interface for Python database access modules. It’s documented in PEP 249 https://www.python.org/dev/peps/pep-0249/. Nearly all Python database modules such as sqlite3, psycopg and mysql-python conform to this interface.
In [1]:
# -*- coding: utf-8 -*-
"""
Created on Mon Aug 15 07:37:10 2016
@author: mayank johri
"""
try:
from sqlite3 import dbapi2 as sqlite
except ImportError:
from pysqlite2 import dbapi2 as sqlite
import os
DB_FILE = 'db/comment.sqlite3'
def createDB():
if os.path.exists(DB_FILE):
os.remove(DB_FILE)
db_conn = sqlite.connect(DB_FILE)
db_curs = db_conn.cursor()
db_curs.execute("""CREATE TABLE people (
id INTEGER PRIMARY KEY, first_name VARCHAR(20),
last_name VARCHAR(30), date_of_birth DATE)""")
db_curs.execute("""INSERT INTO people (first_name, last_name, date_of_birth)
VALUES ('Mayank', 'Johri', '1976-7-10')""")
db_curs.close()
return db_conn
def getData(db_conn):
db_cur = db_conn.cursor()
sql = "select first_name, last_name, date_of_birth from people"
db_cur.execute(sql)
data = db_cur.fetchall()
db_cur.close()
return data
def getAllData(db_conn):
db_cur = db_conn.cursor()
sql = "select first_name, last_name, date_of_birth from people"
db_cur.execute(sql)
data = db_cur.fetchall()
db_cur.close()
return data
def setUser(db_conn, first_name, last_name, date_of_birth):
stmt = """INSERT INTO people (first_name,
last_name,
date_of_birth) VALUES (?,?,?)"""
db_cur = db_conn.cursor()
db_cur.execute(stmt, (first_name, last_name, date_of_birth))
def rollback(db_conn):
pass
def commit(db_conn):
db_conn.commit()
if __name__ == "__main__":
db_conn = None
try:
db_conn = createDB()
setUser(db_conn, "Subhas", "Chandra Bose", "January 23, 1897")
setUser(db_conn, "Bhagat", "Singh", "September 28, 1907")
data = getAllData(db_conn)
print(type(data))
for d in data:
print("{0} {1} was born in {2}".format(d[0], d[1], d[2]))
except:
rollback(db_conn)
raise
else:
commit(db_conn)
finally:
if db_conn != None:
db_conn.close()
In [1]:
try:
from sqlite3 import dbapi2 as sqlite
except ImportError:
from pysqlite2 import dbapi2 as sqlite
DB_FILE = 'db/comment.sqlite3'
In [1]:
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
# engine.dispose()
engine = create_engine('sqlite:///userlist.db', echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)
Base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session_dec = Session()
ed_user = User(name='Meenu', fullname='Meenakshi Johri', password='meenuInIndia')
print(ed_user)
session.add(ed_user)
print("Ued_user.id")
session.add(User(name='GV',fullname='GV', password='gv@ibm'))
session.flush()
print(ed_user.id)
#Now let’s commit the changes:
session.commit()
# #SQLAlchemy sends the COMMIT statement that permanently commits the flushed changes and ends the transaction.
# # Delete
# #To delete the test_page object from the database you would use:
session.delete(ed_user)
session.flush()
# #At this point you can either commit the transaction or do a rollback. Let’s do a rollback this time:
session.commit()
session.close()
engine.dispose()
In [5]:
# from sqlalchemy import create_engine, ForeignKey
# from sqlalchemy import Column, Date, Integer, String
# from sqlalchemy.ext.declarative import declarative_base
# from sqlalchemy.orm import relationship
# # engine.dispose()
# engine = create_engine('sqlite:///userlist.db', echo=True)
# Base = declarative_base()
# class User(Base):
# __tablename__ = 'users'
# id = Column(Integer, primary_key=True)
# name = Column(String)
# def __repr__(self):
# return "<User(name='%s', fullname='%s', password='%s')>" % (self.name, self.fullname, self.password)
# class Address(Base):
# __tablename__ = 'address'
# address_id = Column(Integer, primary_key=True)
# house_name = Column(String)
# house_no = Column(String)
# city = Column(String)
# user_id = Column(Integer, ForeignKey('User.id'))
# Base.metadata.create_all(engine)
In [ ]:
# from sqlalchemy.orm import sessionmaker
# Session = sessionmaker(bind=engine)
# session = Session()
# session_dec = Session()
# ed_user = User(name='Meenu', fullname='Meenakshi Johri', password='meenuInIndia')
# # ed_user.address = Address(house_no="20", house_name="Raj Ghar", city= "Jaipur")
# session.commit()
# session.close()
# engine.dispose()
In [9]:
import json # https://docs.python.org/3/library/json.html
import requests # https://github.com/kennethreitz/requests
import records # https://github.com/kennethreitz/records
# randomuser.me generates random 'user' data (name, email, addr, phone number, etc)
r = requests.get('http://api.randomuser.me/0.6/?nat=us&results=3')
j = r.json()['results']
# Valid SQLite URL forms are:
# sqlite:///:memory: (or, sqlite://)
# sqlite:///relative/path/to/file.db
# sqlite:////absolute/path/to/file.db
# records will create this db on disk if 'users.db' doesn't exist already
db = records.Database('sqlite:///users.db')
db.query('DROP TABLE IF EXISTS persons')
db.query('CREATE TABLE persons (key int PRIMARY KEY, fname text, lname text, email text)')
for rec in j:
user = rec['user']
name = user['name']
key = user['registered']
fname = name['first']
lname = name['last']
email = user['email']
db.query('INSERT INTO persons (key, fname, lname, email) VALUES(:key, :fname, :lname, :email)',
key=key, fname=fname, lname=lname, email=email)
rows = db.query('SELECT * FROM persons')
print(rows.export('csv'))
SQLObject is yet another ORM. It supports a wide variety of databases: Common database systems MySQL, Postgres and SQLite and more exotic systems like SAP DB, SyBase and MSSQL.
SQLObject is a popular Object Relational Manager for providing an object interface to your database, with tables as classes, rows as instances, and columns as attributes.
SQLObject includes a Python-object-based query language that makes SQL more abstract, and provides substantial database independence for applications.
In [ ]:
import sqlobject
from sqlobject.sqlite import builder
conn = builder()('sqlobject_demo.db')
class PhoneNumber(sqlobject.SQLObject):
_connection = conn
number = sqlobject.StringCol(length=14, unique=True)
owner = sqlobject.StringCol(length=255)
lastCall = sqlobject.DateTimeCol(default=None)
PhoneNumber.createTable(ifNotExists=True)
myPhone = PhoneNumber(number='(415) 555-1212',
owner='Leonard Richardson')
In [ ]:
# Running code with partial information will result in error
duplicatePhone = PhoneNumber(number="(415) 555-1212")
In [ ]:
duplicatePhone = PhoneNumber(number="(415) 555-1212")
In [ ]:
import sqlobject
from sqlobject.sqlite import builder
conn = builder()('sqlobject_demo_relationships.db')
class PhoneNumber(sqlobject.SQLObject):
_connection = conn
number = sqlobject.StringCol(length=14, unique=True)
owner = sqlobject.ForeignKey('Person')
lastCall = sqlobject.DateTimeCol(default=None)
class Person(sqlobject.SQLObject):
_idName='fooID'
_connection = conn
name = sqlobject.StringCol(length=255)
#The SQLObject-defined name for the "owner" field of PhoneNumber
#is "owner_id" since it's a reference to another table's primary
#key.
numbers = sqlobject.MultipleJoin('PhoneNumber', joinColumn='owner_id')
Person.createTable(ifNotExists=True)
PhoneNumber.createTable(ifNotExists=True)
In [ ]:
person = Person(name='Vinay')
p = PhoneNumber(number="2222", owner=person)