Python ORM Framework SQLAlchemy Learning Notes installation and simple query examples

Recently I was looking for a Python database orm (Object relational Mapper), SQLAlchemy (Project home page) This open source project came into my sight and wanted to try to use Django's ORM module, But Django's modular connections are relatively tight, not separated separately, to some extent, the Django self-built ecosystem brings us a fast and convenient development environment while sacrificing the flexibility of assembly.

The first study, also did not really feel the benefits of SQLAlchemy, but see its introduction of many large companies have adopted the project, and its supporting database is quite rich, so I think it is worthwhile to spend some time to study. However, it is regrettable that the Chinese information about SQLAlchemy is relatively small, so it has brought some trouble to our poor English.

The best way to study a project is to read its official documentation, which, of course, easily finds the SQLAlchemy document (0.7). The format of the document is the same as for most projects, there are download installation instructions, examples, and quick start tutorials. But I'm 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 wrong place also want to point out.

1. Installing SQLAlchemy

The installation section is not intended to be described in detail, and can be installed via Easy_install or PIP, with the following commands:

The code is as follows:

Easy_install SQLAlchemy
# or
Pip Install SQLAlchemy

Of course I am using a Windows environment, so prefer to use setup.py installation, download the compressed package, unzip, then switch to the directory at the command prompt, then run the following command:

The code is as follows:

Python setup.py Install

It is important to note that the default installation compiles and installs C extensions, which are compiled directly into binary native code and then accelerated for SQLAlchemy processing data sets, which is a good feature, unfortunately, Windows prompts for a compiler installation extension failure, Of course this does not affect the use of sqlalchemy, just as a performance optimization, the native development environment can not need these extensions, if you do not need to try the following command:

The code is as follows:

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

OK, here I have a brief introduction to the installation section, if you are interested in this section, you can go to the document.

Finally, you can check the installation results:

The code is as follows:

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

2. Simple query

Just as any new language is from the Almighty ' Hello World ' beginning, the first simple experience of a sqlalchemy, because SQLAlchemy is the management database, so we need a database, since the use of Python, a reference to the database, to do experiments with the brunt of the SQLite3 is Python, this time we even sqlit e database files do not need to specify, directly create a memory-based database, that is, the data files stored in memory, easy for us to test.

We use Create_engine to create the database connection engine:

The code is as follows:

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

Create_engine's first argument ' sqlite:///:memory: ' We know it's a database connection, what's the second parameter echo=true do, in fact, if echo= True then SQLAlchemy will output the log through the Python standard module logging, and 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 you do not want to sqlalchemy so verbose can be set to false, so that the information is not visible.

Create_engine () will return an engine instance (instance), which represents SQLAlchemy's core interface to the database, which hides the details of various database dialects (dialect). The bottom of the sqlalchemy is actually Python's dbapi.

It is important to note that there is no actual connection to the database at this time, and when is the connection to the database really established? This only happens when you query the database for the first time. Uh... This is a bit like lazy Loading (lazy loading, delayed loading), which means we really need to make a connection when we actually have to manipulate the database. SQLAlchemy a lot of places to use the Lazyload, the future will have the opportunity to introduce you.

Next we will execute the first SQL statement while establishing the database connection:

The code is as follows:

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

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

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

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

