Recently, it happens to be seeking a Python database ORM (Object Relational Mapper), SQLAlchemy (project home page) this open source project into my sight , I would have liked to try to use Django's ORM module , but no matter how Django's modules are more tightly linked , did not separate down to a certain extent . Django self-contained ecosystem in bringing us a fast and convenient development environment at the same time sacrificed the flexibility of assembly.
The first time I learned, but did not feel the benefits of SQLAlchemy, but see its introduction of many large companies are using the project, and its support for databases is still 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 for our poor English has brought some trouble.
The best way to research a project is to read its official documentation, and of course it was easy to find the SQLAlchemy documentation (0.7). The format of the documentation is the same as most projects, there are instructions for downloading and installing, examples, and quick start tutorials. However, I'm used to downloading a PDF and studying it.
The following will be my recent reading and learning to make a note, of course, this is only for reference, there may be some of their own guesses and ideas, not as an authoritative basis, improper also hope to point out.
1. Install SQLAlchemy
The installation part is not going to be detailed, it can be done via easy_install or pip with the following commands:
# Or
pip install SQLAlchemy
Of course I'm using a Windows environment, so I tend to use install, download the zip, unzip it, and then switch to that directory at the command prompt, then run the following command:
python install
It should be noted that the default installation will compile and install C extensions, these C extensions will be compiled directly to the binary native code and then accelerate the processing of datasets for SQLAlchemy, this is a very good feature, unfortunately, Windows prompted to compile and install the extensions failed, of course, this does not affect the use of SQLAlchemy, but only as a performance optimization, the development environment of the machine can not need these extensions, if you do not need to try the following command:
pip install --global-option='--without-cextensions' SQLAlchemy
# Or the way
python --without-cextensions install
Well, I've briefly covered the installation part here, so if you're interested in this part you can move to the documentation.
Finally, you can test the results of the installation:
>>> import sqlalchemy
>>> sqlalchemy.__version__
0.7.0
2. Simple queries
Just like any new language starts with the almighty 'Hello World', let's have a brief experience with SQLAlchemy first. Since SQLAlchemy manages databases, we need a database, and since we've been using Python, when it comes to databases, the database that we use to do our experiments is Python's own SQLite3, which we're going to use for the first time. This time we do not even need to specify the SQLite database file, directly create a memory-based database, that is to say, the data files stored in memory, easy for us to test the following.
We use create_engine to create the database connection engine:
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
The first parameter of create_engine 'sqlite:///:memory:' we know is to establish a database connection, then the second parameter echo=True is to do what, in fact, if echo=True then SQLAlchemy will be through the Python standard module logging to output logs, if you If you are operating the interactive command console, some information will be output, here we may see some SQL statements generated by SQLAlchemy, this is necessary for us to learn and debugging, so here we set it to True, otherwise, if you do not want to SQLAlchemy so verbose then you can set it to False, so that you can not see the information. information.
create_engine() will return an Engine engine instance (instance), which represents SQLAlchemy's core interface to the database, which hides the details of the various database dialects (dialect), in fact, the underlying SQLAlchemy is Python's DBAPI.
It is important to note that at this point no connection is essentially established to the database, when will a connection actually be established to the database? This will only happen the first time you query the database. Uh... this is kind of like Lazy Loading, which means that we need to actually manipulate the database before we actually establish a connection.SQLAlchemy uses Lazyload in a number of ways, which I'll have a chance to talk to you about in the future.
Next we'll execute the first SQL statement while establishing a database connection:
>>> ("select 1").scalar()
1
Well, when executed, Engine finally establishes what is essentially a database connection.
Engine's management of database connections takes the form of a database connection pool. When a connection is first established, SQLAlchemy will place the established connection into the internal connection pool to be reused for subsequent data manipulation statements.
Of course the usage about Engine is not the wonderful ORM part of SQLAlchemy, subsequently we will cover binding Engine to ORM and then using objects to manipulate the database part.