Skip to content

sqlalchemy-SQL-Expression-Language-Tutorial

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History
 
 

sqlalchemy-SQL-Expression-Language-Tutorial

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
README.md
README.md
 
 

README.md

SQL Expression Language Tutorial

Connecting

  • Return value of create_engine is an instance of Engine
  • First time Engine.connect() or Engine.execute() is called, we establish a real DBAPI connection to the database

Define and Create Tables

  • Column is usually represented by object called Column
  • Table object is called Table
  • Define tables within a catalog called MetaData
In [6]: from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey

In [7]: metadata = MetaData()

In [8]: users = Table('users', metadata,
   ...:    Column('id', Integer, primary_key=True),
   ...:    Column('name', String),
   ...:    Column('fullname', String)
   ...: )

In [9]: addresses = Table('addresses', metadata,
   ...:    Column('id', Integer, primary_key=True),
   ...:    Column('user_id', None, ForeignKey('users.id')),
   ...:    Column('email_address', String, nullable=False)
   ...: )
  • Then use create_all(engine) to actually create the tables. The method is idempotent and can be called multiple times with impunity.

Insert Expressions

  • Insert construct, represents an insert statement

    • Get str representation to see SQL generated
    In [15]: ins = users.insert()

In [16]: str(ins)
Out[16]: 'INSERT INTO users (id, name, fullname) VALUES (:id, :name, :fullname)'
    • Use values() to limit insert statement to certain values
    In [18]: ins = users.insert().values(name='bill', fullname='jacky hacky')

In [19]: str(ins)
Out[19]: 'INSERT INTO users (name, fullname) VALUES (:name, :fullname)'

In [20]: ins.compile().params
Out[20]: {'fullname': 'jacky hacky', 'name': 'bill'}

Executing

  • To connect to a database, use the engine.connect() method. It returns an instance of Connection
  • The returned object, has a connect() method which you can pass a ins object into.
  • This returns a ResultProxy object.
In [27]: type(result)
Out[27]: sqlalchemy.engine.result.ResultProxy
  • This includes an result.inserted_primary_key attribute which you can use to get the key of the inserted object

Executing Multiple Statements

  • Can pass in a instance of Insert into conn.execute to insert in one line.
  • Passing a list of dicts as second param allows you to perform multiple inserts, like so:
In [32]: conn.execute(users.insert(), [
   {'name': 'john', 'fullname': 'john magoo'},
   {'name': 'bob', 'fullname': 'bobo'}
])

Selecting

  • Use select function which returns an instance of sqlalchemy.sql.selectable.Select which can be passed into conn.execute
  • This returns a ResultProxy which can be iterated through like so:
In [37]: for r in result: print r
(1, u'bill', u'jacky hacky')
(2, u'john', u'john p')
(3, u'lex', u'Hello')
(4, u'john', u'john magoo')
(5, u'bob', u'bobo')
  • Can access the values using array-indexes, dict keys or attributes, as follows:
In [41]: result = conn.execute(s).fetchone()
In [42]: result['name']
Out[42]: u'bill'

In [43]: result.name
Out[43]: u'bill'

In [44]: result[1]
Out[44]: u'bill'
  • Call result.close() to return connection to connection pool.
  • Use .where() called against the Select object to add filter expressions

Operators

  • Using Python operators again Column objects, will generator SQL equivalent:
In [45]: print users.c.id == addresses.c.id
users.id = addresses.id

In [47]: print users.c.id != None
users.id IS NOT NULL

In [48]: print users.c.id == None
users.id IS NULL
  • Can generator operators using .op('OPERATOR')(value) method

Conjunctions

  • Import from sqlalchemy.sql to use
In [59]: from sqlalchemy.sql import and_, or_, not_

In [60]: print or_(users.c.id < 100, users.c.id > 2)
users.id < :id_1 OR users.id > :id_2

In [61]: print and_(users.c.id < 100, users.c.id.like('%l'))
users.id < :id_1 AND users.id LIKE :id_2
  • The where method can be chained

Using Joins

  • Aside from manually performing joins, they can also be performed with the join method.