Installation and simple query of Python ORM framework SQLAlchemy learning notes

Recently, I was looking for a Python database ORM (Object Relational Mapper). The open-source project SQLAlchemy (Project homepage) came into my sight, I was trying to use Django's ORM module, but Django's modules were closely linked and could not be split separately, to a certain extent, it indicates that Django's self-contained ecosystem sacrifices the flexibility of assembly while bringing us a fast and convenient development environment.

I didn't really feel the benefits of SQLAlchemy when I first studied it. However, many of the companies that I introduced use this project, and the databases it supports are quite rich, so I think it is worthwhile to spend some time researching. Unfortunately, SQLAlchemy has a small amount of Chinese materials, which makes it difficult for us to use poor English.

The best way to study a project is to read its official documentation. Of course, you can easily find the SQLAlchemy documentation (0.7 ). The document format is the same as that for most projects. Download and install instructions, examples, and quick start tutorials are provided. However, I am still used to downloading a PDF file for further research.

Next I will take a note of my recent reading and learning. Of course, this is for reference only. I may have some guesses and ideas in it, but I hope to point out some improper ideas.

1. Install SQLAlchemy

The installation part is not described in detail. You can install it through easy_install or pip. The command is as follows:
Copy codeThe Code is as follows: easy_install SQLAlchemy
# Or
Pip install SQLAlchemy
Of course, I am using a Windows environment, so I prefer to use setup. py to install, download the compressed package, decompress the package, switch to the directory at the command prompt, and then run the following command:
Copy codeThe Code is as follows:
Python setup. py install
Note that C extensions will be compiled and installed by default. These C extensions will be directly compiled into binary native code and then processed for SQLAlchemy for dataset acceleration. This is a very good feature, unfortunately, Windows prompts that the compilation and installation of extensions failed. Of course, this does not affect the use of SQLAlchemy, but is only used for performance optimization. These extensions are not required in the local development environment, if not, try the following command:
Copy codeThe Code is as follows:
Pip install -- global-option = '-- without-cextensions 'sqlalchemy
# Or setup. py
Python setup. py -- without-cextensions install
Now, I will briefly introduce the installation part here. If you are interested in this part, you can proceed to the document.

Finally, check the installation results:
Copy codeThe Code is as follows:
>>> Import sqlalchemy
>>> Sqlalchemy. _ version __
0.7.0

2. Simple Query

Just as any new language starts from the omnipotent 'Hello world', we should first try SQLAlchemy. Because SQLAlchemy is used to manage databases, we need a database, since Python was used, when it comes to databases, the first thing to experiment with is SQLite3, which comes with Python. This time, we don't need to specify any database files for SQLite, directly create a memory-based database, that is to say, the data files are stored in the memory, which is convenient for the following test.

We use create_engine to create a database connection engine:
Copy codeThe Code is as follows:
>>> From sqlalchemy import create_engine
>>> Engine = create_engine ('sqlite: //: memory: ', echo = True)
The first parameter of create_engine is 'sqlite: //: memory: '. We know that the database connection is established. What is the second parameter echo = True, in fact, if echo = True, SQLAlchemy will use the Python standard module logging to output logs. If you operate 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 debug, so here we set it to True. Otherwise, if you do not want SQLAlchemy to be so arrogant, you can set it to False so that you will not be able to see this information.

Create_engine () returns an Engine instance, which represents SQLAlchemy's core database interfaces. It hides the details of various database dialects (dialect, in fact, the underlying layer of SQLAlchemy is Python's DBAPI.

It should be noted that at this time there is no substantial connection to the database. When will the connection be established with the database? This will only happen when you first query the database. Er... This is a bit like Lazy Loading (Lazy Loading, delayed Loading). That is to say, we need to actually create a connection when operating the database. SQLAlchemy uses Lazyload in many places and will be introduced later.

Next we will execute the first SQL statement and establish a database connection:
Copy codeThe Code is as follows:
>>> Engine.exe cute ("select 1"). scalar ()
1
When engine.exe cute is executed, the Engine finally establishes a physical database connection.

The Engine manages the database connection Pool. When the connection is established for the first time, SQLAlchemy puts established connections into the internal connection pool to facilitate reuse of subsequent data operation statements.

Of course, the usage of the Engine is not a wonderful ORM part of SQLAlchemy. We will introduce how to bind the Engine to the ORM and then use the object to operate the database part.