Python ORM Framework SQLAlchemy Learning Notes installation and simple query instance _python

Recently, just looking for a Python database orm (Object relational Mapper), SQLAlchemy (Project homepage) This open source project has come to my attention, trying to use the Django ORM module, But the Django module is relatively close, not separate, to some extent, the Django system ecosystem in the rapid and convenient development environment for us at the same time sacrificing the flexibility of assembly.

First study, there is no real sense of the benefits of SQLAlchemy, but see the introduction of many large companies have adopted the project, and its supporting database is still quite rich, so I think it is worthwhile to spend some time studying. However, it is regrettable that there is less Chinese information about SQLAlchemy, so it is troublesome for us to have bad English.

The best way to study a project is to read its official documentation and, of course, easily find the SQLAlchemy document (0.7). The document has the same format as most projects, with download installation instructions, examples, and quick start tutorials. However, I am still used to downloading a PDF to study slowly.

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

1. Install SQLAlchemy

The installation section is not intended to be described in detail and can be installed via Easy_install or PIP, as follows:

Copy Code code as follows:

Easy_install SQLAlchemy
# or
Pip Install SQLAlchemy

Of course I'm using the Windows environment, so I tend to use setup.py installation, download the package, unzip it, then switch to the directory at the command prompt, and then run the following command:

Copy Code code as follows:

Python setup.py Install

Note here that the default installation will compile and install the C extension, these C extensions will be compiled directly into binary native code and then for SQLAlchemy Processing dataset acceleration, this is a good feature, unfortunately Windows prompts to compile the installation extension failed, Of course, this does not affect the use of sqlalchemy, only as a performance optimization, the native development environment can not need these extensions, if you do not need to try the following command:

Copy Code code as follows:

Pip install--global-option= '--without-cextensions ' SQLAlchemy
# or Setup.py Way
Python setup.py--without-cextensions Install

OK, here's a brief introduction to the installation section, so if you're interested in this section, you can go to the documentation.

Finally, you can check the installation results:

Copy Code code as follows:

>>> Import SQLAlchemy
>>> sqlalchemy.__version__
0.7.0

2. Simple query

Just like any new language is from the Almighty ' Hello World ' Start, the first simple experience a sqlalchemy, because SQLAlchemy is the management of the database, so we need a database, since the use of Python, a reference to the database, take the brunt of the experiment is Python's own SQLite3, this time we even sqlit E's database files do not need to be specified, directly create a database based on memory, which means that the data files stored in memory, easy to our test below.

We use Create_engine to create the database connection engine:

Copy Code code as follows:

>>> from SQLAlchemy import create_engine
>>> engine = Create_engine (' sqlite:///:memory: ', echo=true)

Create_engine's first parameter ' sqlite:///:memory: ' We know is to establish the database connection, that the second parameter echo=true is what to do, in fact if echo= True then SQLAlchemy will output the log via the Python standard module logging, if you are manipulating the interactive command console, some information will be output, and here we may see some SQL statements generated by SQLAlchemy. This is necessary for us to learn and debug, so here we set it to true, otherwise, if we do not want to sqlalchemy so verbose can be set to false, so that the information will not be seen.

Create_engine () will return a engine engine instance (instance) that represents SQLAlchemy's core interface to the database, which hides the details of various database dialects (dialect). In fact, the bottom of SQLAlchemy is Python's dbapi.

It should be noted that there is no substantive connection to the database at this time, and when will the database really be connected? This will only happen when you first query the database. Uh... This is a bit like lazy Loading (lazy loading, deferred loading), which means we really need to actually manipulate the database to actually establish the connection. SQLAlchemy many places to use the Lazyload, later will have the opportunity and everybody introduces.

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

Copy Code code as follows:

>>> Engine.execute ("SELECT 1"). Scalar ()
1

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

Engine for database connection management is the database connection pool (pool), when the connection is first established, SQLAlchemy will put the established connection into the internal connection pool to facilitate subsequent data manipulation statements to be reused.

Of course, the use of engine is not sqlalchemy wonderful ORM part, then we will introduce the engine binding to ORM, and then use the object to manipulate the database section.