SQLian: handles SQL so you don’t need to¶

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

The Main Interface¶

sqlian.connect(url)¶

Create a database connection.

The database URL takes the form:

scheme://username:password@host:port/database

Multiple parts of the form can be omitted if not present. Common examples:

• postgresql:///db connects to the local PostgreSQL instance via Un*x sockets without authentication, to the database db. Psycopg2, the default PostgreSQL driver, is used.
• mysql+pymysql://localhost/db connects to the db database in the MySQL instance at localhost, using no authentication. The PyMySQL driver is used.
• sqlite:///db.sqlite3 connects to the SQLite3 file at relative path db.sqlite3. The third slash is to seperate path from the host. Use four slashes if you need to specify an absolute path. The default driver (built-in sqlite3) is used.

Parameters:	url – URL of the database to connect to.
Returns:	A Database instance with open connection.

class sqlian.UnrecognizableScheme(scheme)¶

Raised when connect() fails to recognize a scheme.

class sqlian.Database(**kwargs)¶

A database connection.

This class provides a wrapper to a DB-API 2.0 Connection instance, offering additional SQL-building methods alongside with the standard API.

Keyord arguments passed to the constructor are used to create the underlying Connection instance. The implementor is responsible for converting them for the underlying DB-API 2.0 interface.

Instances of this class implement the context manager interface. The instance itself is assigned to the as expression, and the connection is closed when the context manager exists, committing done automatically if there are no exceptions.

Parameters:	host – Network location of the database. port – Network port to access the database. database – Name of the database. username – Username to connect to the database. password – Password to connect to the database. options – Database options as a string-string mapping.

close()¶

Close the connection.

This method exists to conform to DB-API 2.0.

commit()¶

Commit any pending transaction to the database.

This method exists to conform to DB-API 2.0.

connect(dbapi, **kwargs)¶

Connect to the database.

This is called by create_connection() to create the connection. Keyword arguments to this method are passed directly from the class constructor.

You should override this method to convert parameter names, call connect() on the passed DB-API 2.0 interface, and return the connection instance.

connection¶

The underlying connection object. This property is read-only.

create_connection(**kwargs)¶

Creates a connection.

If you’re implementing a wrapper not conforming to DB-API 2.0, you should implement this method to override the default behavior, which depends on the API.

Keyword arguments to this method are passed directly from the class constructor.

Returns:	A DB-API 2.0 Connection object.

cursor()¶

Return a new Cursor Object using the connection.

This method exists to conform to DB-API 2.0.

delete(*args, **kwargs)¶

Build and execute a DELETE statement.

execute_statement(statement_builder, args, kwargs)¶

Build a statement, and execute it on the connection.

This method provides implementation of statement construction and execution. Call this method like this when you implement a statement builder in custom database subclasses:

def select(self, *args, **kwargs):
    return self.execute_statement(self.engine.select, args, kwargs)

You generally don’t need to call this method directly as a user, but use one of the wrapper functions like the above instead.

Return type:	RecordCollection

insert(*args, **kwargs)¶

Build and execute an INSERT statement.

is_open()¶

Whether the connection is open.

Return type:	bool

rollback()¶

Rollback pending transaction.

This method exists to conform to DB-API 2.0. Behavior of calling this method on a database not supporting transactions is undefined.

select(*args, **kwargs)¶

Build and execute a SELECT statement.

update(*args, **kwargs)¶

Build and execute an UPDATE statement.

Connecting to Unsupported Databases¶

To connect to a database not yet supported by SQLian, you need to implement your own Database subclass, and optionally register it to SQLian if you want to use it with connect().