Installation of Python ORM framework SQLAlchemy learning notes and simple query examples

Recently just seeking a Python database ORM (Object Relational Mapper), SQLAlchemy this open source project (project site) into the line of sight of me, wanted to try to use Django ORM modules, helpless cheek by jowl with the Django module, failed to spin down alone, to some extent, explain the Django sui generis ecosystem in bring us rapid convenient development environment at the expense of the flexibility of assembly at the same time.

For the first time, I didn't really feel the benefits of SQLAlchemy, but many of the major companies it introduced adopted the project, and the databases it supported were quite rich, so I thought it was worth the time to study. Unfortunately, there is little Chinese material on SQLAlchemy, so it is a problem for those of us with poor English.

The best way to research a project is to read its official documentation, and of course it's easy to find the documentation for SQLAlchemy (0.7). The documentation is in the same format as most projects, with download and installation instructions, samples, and quick start tutorials. But I'm still used to downloading a PDF and studying it slowly.

The following will be my recent reading study to make a note, of course, this is only for reference, there may be some of their own guesses and ideas, do not make an authoritative basis, improper also want to point out.

1. Install the SQLAlchemy

The installation section is not intended to be detailed, but can be installed via easy_install or PIP, as follows:

easy_install SQLAlchemy
#  or 
pip install SQLAlchemy

Of course, I use a Windows environment, so I prefer to install setup.py, download the zip, unzip, then switch to the directory at the command prompt and run the following command:

python setup.py install

Note here is the default installation will compile C extensions, these C extensions will be compiled into the binary native code and then directly handle data set for SQLAlchemy acceleration, this is a very nice feature, it is a pity that under Windows tips compiled installation failure, of course, this does not affect the use of SQLAlchemy, just as a performance optimization, the native development environment can do not need these extensions, if you don't need to try the following command:

pip install --global-option='--without-cextensions' SQLAlchemy
#  or setup.py way 
python setup.py --without-cextensions install

Okay, so I'm done with the installation section here, and if you're interested in this section you can move to the documentation.

Finally, you can check the installation results:

>>> import sqlalchemy
>>> sqlalchemy.__version__ 
0.7.0

2. Simple queries

Like any new language starts, everything 'Hello World' simple experience a SQLAlchemy, first because of SQLAlchemy is management database, so we need a database, since the use of Python, when it comes to the database, to bear the brunt of the experiment is to Python's built-in SQLite3, this time we don't even need to specify the SQLite database files, directly to create a database based on memory, data file that is stored in memory, for us the following test.

We use create_engine to create a database connection engine:

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

Create_engine the first parameter to the 'sqlite: / / / : memory:' we know that is establishing a database connection, the second parameter echo = True is what to do, in fact, if echo = True the SQLAlchemy will through the Python standard module logging output logs, if you are in the interactive command console operation, some information will be output, here we may see SQLAlchemy generated some SQL statements, this is very necessary for us to study and debugging, So here we're going to set it to True, otherwise, if you don't want SQLAlchemy to be so verbose you can set it to False, and you won't see the information.

Create_engine () will return an Engine Engine instance (instance) that represents SQLAlchemy's core interface to the database, which hides the details of various database dialects, and actually SQLAlchemy's underlying DBAPI is Python.

It is important to note that you are not actually connecting to the database at this point. When will you actually connect to the database? This only happens when you first query the database. Uh... This is a bit like Lazy Loading (Lazy Loading, Lazy Loading), which means that we don't really establish a connection until we actually manipulate the database. SQLAlchemy USES Lazyload in a number of ways, and we'll have a chance to talk about that in the future.

Next, we execute the first SQL statement and establish a database connection:

>>> engine.execute("select 1").scalar()
1

Well, when engine.execute executes, engine finally establishes a virtual database connection.

Engine manages database connections in a database connection Pool (Pool), and when the connection is first established, SQLAlchemy will place the established connection in an internal connection Pool for reuse when subsequent data manipulation statements are executed.

Of course, the use of Engine is not the exciting ORM part of SQLAlchemy, and we'll talk about binding Engine to ORM and then using objects to manipulate the database part.