Introduction to SQLAlchemy

By : Yogesh Kumar

SQLAlchemy

The breadth of SQLAlchemy’s SQL rendering engine, DBAPI integration, transaction integration, and schema description services are documented here. In contrast to the ORM’s domain-centric mode of usage, the SQL Expression Language provides a schema-centric usage paradigm.

SQLAlchemy has two main parts:
- Core
- ORM (Object Relational Mapper)

In this blog, we’ll only discuss about SqlAlchemy Core.

### Core

SQLAlchemy mainly consists of following:
- Engine
- Connection
- Dialect
- Metadata
- Table
- Column

## Engine
It’s the starting point for SQLAlchemy and used to connect to databases and it‘s API.
## Connection
Connection provides functionality for a wrapped DB-API connection. It is used to execute SQL statements on a database. (Note: Connection is not thread-safe)
## Dialect
Dialect defines the behaviour of a specific database and DB-API combination. Dialect also handles query generation, execution, result handling and much more that differs from other databases.
## Metadata
Metadata is bound to an engine or a connection. It holds the Table and Columns metadata in itself.
## Table
Table object is used to represent a table stored in the database. Its stored in Metadata.

### Column
Represents a column in a table.
Column have some important parameters:
- index=bool
- nullable=bool
- primary_key=bool
- unique=bool
- default=callable/scalar
- onupdate=callable/scalar
- autoincrement=bool

### Connecting to a database:

For this tutorial we will use an in-memory-only SQLite database. This is an easy way to test things without needing to have an actual database defined anywhere. To connect we use create_engine():

from sqlalchemy import create_engine
engine = create_engine()

Note: Database connection string is dependent on the database you’re using.
Here are some of the examples:

postgresql://:@:/
sqlite:///

Rest of the configuraions can be found at http://docs.sqlalchemy.org/en/latest/core/engines.html

The return value of create_engine() is an instance of Engine, and it represents the core interface to the database, adapted through a dialect that handles the details of the database and DBAPI in use. In this case the SQLite dialect will interpret instructions to the Python built-in sqlite3 module.

### Creating Tables:
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
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)
)

Column Types:
Integer, BigInteger, String, Boolean, Unicode, UnicodeText, Date, Datetime, Text, Time
and other SQL standard datatypes

Inserting values into a Table:
ins = users.insert().values(name=’john’, fullname=’John Doe’)
Alternate way of insert:
ins = users.insert()
conn.execute(ins, id=2, name='wendy', fullname='Wendy Williams')
Multiple values at ones:
conn.execute(addresses.insert(), [
{'user_id': 1, 'email_address' : 'jack@yahoo.com'}, /> {'user_id': 1, 'email_address' : 'jack@msn.com'}, /> {'user_id': 2, 'email_address' : 'www@www.org'}, /> {'user_id': 2, 'email_address' : 'wendy@aol.com'}, /> ])

Executing insert statement:
engine = create_engine(‘sqlite:///test.db’)
conn = engine.connect()
conn.execute(ins)

Operators:
conn.execute(select([addresses]).where(addresses.c.user_id == 1)).fetchall()
conn.execute(select([addresses]).where(addresses.c.user_id != 1)).fetchall()
conn.execute(select([addresses]).where(addresses.c.user_id > 1)).fetchall()

Selecting column(s) from a table:
Selecting all the columns from a table:
from sqlalchemy.sql import select
s = select([users])
Selecting particular columns from a table:
s = select([users.c.full_name])
Adding a label to selected column
s = select([users.c.name.label(‘new_label’)])
Selecting with conditions (where, like, ilike):
s = select([users]).where(users.c.name == ‘John’)
from sqlalchemy.sql import and_, or_, not_
s = select([users]).where(or_(users.c.name == ‘John’, users.c.name=’Jon’))
s = select([users]).where(users.c.full_name.like == ‘John%’)

Displaying results:
result = conn.execute(s)
for row in result:
print(row)
(1, u'john', u'John Doe')
(2, u'wendy', u'Wendy Williams')

Updating a table:
stmt = users.update().where(user.c.id == 2).values(
name=’james’, full_name=’James Doe’)
conn.execute(stmt)

Deleting entries:
stmt = users.delete().where(user.c.id_in([2,4,6]))
conn.execute(stmt)

Ordering or Grouping by label:
from sqlalchemy import func, desc
stmt = select(users).order_by(users.c.id)
result = conn.execute(stmt)

stmt = select([users.c.full_nam e,
func.count(addresses.c.user_id).label(‘num_of_addresses’)).\
order_by(‘num_of_addresses’)

Operators:
conn.execute(select([addresses]).where(addresses.c.user_id == 1)).fetchall()
conn.execute(select([addresses]).where(addresses.c.user_id != 1)).fetchall()
conn.execute(select([addresses]).where(addresses.c.user_id > 1)).fetchall()

### Func:
A SQL function generator with attribute access. func generates any SQL function is created based on the word you choose.
from sqlalchemy import func
func.any_arbitrary_function()
any_arbitrary_function()

Conjunctions:
from sqlalchemy.sql import and_, or_, not_
print(and_(
users.c.name.like('j%'),
users.c.id == addresses.c.user_id,
or_(
addresses.c.email_address ==
'wendy@aol.com', /> addresses.c.email_address == 'jack@yahoo.com' /> ),
not_(users.c.id > 5)
)
)

Note:
In case of any error, if the consecutive query fails to work, run ‘conn.begin().rollback()’.

### Terminology:
Table Reflection: Importing whole sets of Table objects automatically from an existing database.


Can we help you build amazing apps? Contact us today.

Topics : sqlalchemy
© Agiliq, 2009-2012