Quickstart¶
SQLian is composed of three main parts:
- Databases represent database connections.
- Statement builders take native objects and convert them to a SQL command string. The built command are then passed to the associated database connection to be executed.
- Records are returned by database queries. They offer a clean, nice interface to interact with the data retrieved from the database.
Let’s do a quick walk through on them one by one.
Connecting to a database¶
SQLian uses the 12factor-inspired database URL syntax to describe a database. This syntax is compatible with popular tools, including DJ-Database-URL, SQLAlchemy, and everything that builds on top of them. Which means, like, everything?
As an example, let’s connect to a PostgreSQL database:
import sqlian
db = sqlian.connect('postgresql://user:pa55@localhost/contactbook')
SQLian has some database support built-in. Some databases require extra
dependencies to actually connect to, like psycopg2
for PostgreSQL. You
can also build your own database support, but we’ll save that discussion for
later.
The connect()
function returns a Database
instance, which
conforms to the DB-API 2.0 specification (PEP 249), so you can get to work
directly if you know your way around. But there’s a better way to do it.
Issuing commands¶
The Database
instance provides a rich set of “statement builders” that
format and execute SQL for you, and make it easier to convert native Python
objects for SQL usage.
Inserting data:
db.insert('person', values={
'name': 'Mosky',
'occupation': 'Pinkoi',
'main_language': 'Python',
})
This roughly translates to:
INSERT INTO "person" ("name", "occupation", "main_language")
VALUES ('Mosky', 'Pinkoi', 'Python')
but saves you from dealing with column and value clauses and all those
%(name)s
stuff.
You can still use column name–value sequences if you wish to:
db.insert(
'person',
columns=('name', 'occupation', 'main_language'),
values=[
('Tim', 'GilaCloud', 'Python'),
('Adam', 'Pinkoi', 'JavaScript'),
],
)
Did I mention you can insert multiple rows at one go? Yeah, you can.
It’s also easy to update data:
db.update('person', where={'name': 'Adam'}, set={'main_language': 'CSS'})
Notice the key ordering does not matter.
You’d guess how deletion works by now, so let’s add a little twist:
db.delete('person', where={'occupation !=': 'Pinkoi'})
The builders automatically parse trailing operators and do the right thing.
Handling results¶
Some statements produce data. For every such query, SQLian returns an iterable
object RecordCollection
so you can handle them.
>>> rows = db.select(sqlian.star, from_='person')
>>> rows
<RecordCollection (pending)>
Accessing the content in any way automatically resolves it:
>>> rows[0]
<Record {"name": "Mosky", "occupation": "Pinkoi", "main_language": "Python"}>
>>> rows
<RecordCollection (1+ rows, pending)>
>>> for row in rows:
... print(row)
<Record {"name": "Mosky", "occupation": "Pinkoi", "main_language": "Python"}>
<Record {"name": "Adam", "occupation": "Pinkoi", "main_language": "CSS"}>
>>> rows
<RecordCollection (2 rows)>
A Record
can be accessed like a sequence, mapping, or even object:
>>> row = rows[0]
>>> row[0]
'Mosky'
>>> row['occupation']
Pinkoi
>>> row.main_language
Python