Shulou(Shulou.com)06/01 Report--

The full text is about 7991 words and may take 12 minutes to read.

Environment: Ubuntu 15.10 64-bit

SQLAlchemy is the ORM framework of Python. Its idea is that the magnitude and performance of the database are more important than the collection of objects, while the abstraction of the collection of objects is more important than tables and rows.

Installation

Install directly through pip:

$pip install sqlalchemy

Open Python and test whether the installation is successful:

> import sqlalchemy > sqlalchemy.__version__'1.0.9' creation engine SQLite

First, take SQLite as an example, because it is relatively simple.

From sqlalchemy import create_engine, MetaDataengine = create_engine ('sqlite:///foo.db', echo=True) metadata = MetaData (engine)

The parameter sqlite:///foo.db is interpreted as:

Sqlite:///

Where foo.db is the relative path. It can also be written as:

Sqlite:///./foo.db

By default, SQLAlchemy uses Python's built-in sqlite3 module to connect to or create SQLite databases. After executing create_engine, you can find that there are many foo.db files in the current directory, so you might as well open it with sqlite.

$sqlite3 foo.dbSQLite version 3.8.11.1 2015-07-29 20:00:57Enter ".help" for usage hints.sqlite > .tables

Note that sqlite3 is used here instead of sqlite, because foo.db is created through Python's built-in sqlite3 module.

MySQL

Let's take a look at how to create an engine when connecting to a MySQL. The subsequent examples in this article are all based on MySQL, which is different from official documentation. First create a test database in MySQL: sa_test, based on which the subsequent examples will be based.

Mysql > CREATE DATABASE sa_test DEFAULT CHARACTER SET UTF8;from sqlalchemy import create_engine, MetaDataengine = create_engine ('mysql+mysqldb://root:*@localhost/sa_test', echo=True) metadata = MetaData (engine)

The parameters here look more complex, and the complete format is:

Dialect+driver://username:password@host:port/database

Here driver uses mysqldb. For more information, please see: MySQLdb:Python operates MySQL database.

For more information on engine configuration, please refer to the official document: Engine Configuration

MetaData

Previously, the engine was bound when creating the MetaData:

Metadata = MetaData (engine)

Of course, it can also be unbound. The advantage of binding is that many subsequent calls (such as MetaData.create_all (), Table.create (), and so on) do not have to specify the engine.

Create a tabl

Create two tables with a foreign key of user id in the user and address,address tables. Note: table names do not use the plural as official documents and many people recommend. It is just a personal preference. For more information, please see StackOverflow: Table NamingDilemma: Singular vs. Plural Names

From sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKeyengine = create_engine ('mysql+mysqldb://root:*@localhost/sa_test', echo=True) metadata = MetaData (engine) user_table = Table (' user', metadata, Column ('id', Integer, primary_key=True), Column (' name', String (50), Column ('fullname', String (100)) address_table = Table (' address') Metadata, Column ('id', Integer, primary_key=True), Column (' user_id', None, ForeignKey ('user.id')), Column (' email', String, nullable=False)) metadata.create_all ()

After executing the sentence metadata.create_all (), the two tables are created and can be viewed immediately in MySQL.

MetaData.create all () can be called multiple times without reporting an error, and it internally checks whether the table has been created. Because MetaData was already bound to the engine when it was created, it is no longer necessary to specify create all () here, otherwise it will be written as follows:

Metadata.create_all (engine)

When the engine is created, the echo parameter is True, and a lot of debugging information is printed when the program is running. In this debugging information, you can see the following two CREATE TABLE statements for MySQL:

CREATE TABLE user (id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR (50), fullname VARCHAR (100), PRIMARY KEY (id)) CREATE TABLE address (id INTEGER NOT NULL AUTO_INCREMENT, user_id INTEGER, email VARCHAR (128C) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES user (id))

In addition to metadata.create_all (), Table has its own create method:

Create (bind=None, checkfirst=False)

The parameter bind generally refers to the engine. The parameter checkfirst indicates whether the check table already exists. For True, if the table already exists, no error is reported, but nothing is done; for False, if the table already exists, an exception will be thrown. Use this method to create these two tables:

User_table.create (checkfirst=True) address_table.create (checkfirst=True)

The bind parameter is ignored here, because the engine is bound when the MetaData object is created, and metadata is passed when the table object is created, so the table itself knows about the engine. If you adjust the order in which the tables are created, you will get an error because there is a foreign key for the user table in the address table, and the user table has not been created yet. So, it's recommended to use MetaData.create_all (), which also checks to see if the table already exists.

Reflection Table Reflection of the table

Once the table is created, it usually doesn't move. So in practical application, tables often already exist and do not need to be created, just "import" them, and then use the autoload parameter.

From sqlalchemy import create_engine, MetaData, Tableengine = create_engine ('mysql+mysqldb://root:*@localhost/sa_test', echo=False) metadata = MetaData (engine) user_table = Table (' user', metadata, autoload=True) print 'user' in metadata.tablesprint [c.name for c in user_table.columns] address_table = Table (' address', metadata, autoload=True) print 'address' in metadata.tables

Output:

True ['id',' name', 'fullname'] True

If MetaData is not bound to the engine, you need to specify the autoload_with parameter:

User_table = Table ('user', metadata, autoload=True, autoload_with=engine)

If the reflected foreign table key references another table, the referenced table is also reflected. For example, only the address table is reflected, and the user table is also reflected.

From sqlalchemy import create_engine, MetaData, Tableengine = create_engine ('mysql+mysqldb://root:*@localhost/sa_test', echo=False) metadata = MetaData (engine) address_table = Table (' address', metadata, autoload=True) print 'user' in metadata.tablesprint' address' in metadata.tables

Output:

TrueTrue insert data

Before you can insert data, you must have table objects, whether newly created or imported through reflection.

Insert object

To insert data into the table, first create an Insert object:

Ins = user_table.insert () print ins

By printing the Insert object, you can see its corresponding SQL statement:

INSERT INTO user (id, name, fullname) VALUES (s, s, s)

If the connected database is not MySQL but SQLite, the output might look like this:

INSERT INTO user (id, name, fullname) VALUES (?

It can be seen that SQLAlchemy helps us encapsulate the differences in syntax between different databases. If the MetaData was created without an engine bound, the output would be slightly different:

INSERT INTO "user" (id, name, fullname) VALUES (: id,: name,: fullname)

At this time, SQLAlchemy does not know the specific database syntax, the table name is in quotation marks ("user"), and the column name is changed to a general format such as id. In addition, this INSERT statement lists each column in the user table, while id is generally not specified when it is inserted, and can be restricted by the Insert.values () method:

Ins = ins.values (name='adam', fullname='Adam Gu') print ins

After the restriction, the id column is no longer available:

INSERT INTO user (name, fullname) VALUES (% s,% s)

You can see that the values () method restricts the columns contained in the INSERT statement. But the name and fullname values we specified are not printed, and they are stored in the Insert object and will only be used when executed.

Execution

The engine we've been talking about can be understood as a repository of database connection objects through which specific SQL statements can be sent to the database. Call the engine's connect () method to get a connection:

Conn = engine.connect ()

Now throw the previous Insert object to it to execute:

Result = conn.execute (ins)

The specific INSERT statement can be seen from the debugging information:

INSERT INTO user (name, fullname) VALUES (% s,% s) ('adam',' Adam Gu') COMMIT

The return value result is a ResultProxy object, and ResultProxy is an encapsulation of cursor in DB- API. The result of the insert statement is not commonly used, but the query statement must use it. Take a look at the data you just inserted in MySQL.

Mysql > select * from user;+----+ | id | name | fullname | +-- + | 1 | adam | Adam Gu | +-+ 1 row in set (0.00 sec) executes multiple statements

Remember that the previous Insert object used the values () method to restrict columns?

Ins = ins.values (name='adam', fullname='Adam Gu')

This approach is actually not conducive to the reuse of Insert objects, and it is better to pass the parameters in through the execute () method:

Ins = user_table.insert () conn.execute (ins, name='adam', fullname='Adam Gu')

The Insert object itself still contains all the columns, and ultimately the columns in the INSERT statement are determined by the parameters of execute (). The specific INSERT statement can be seen from the debugging information:

INSERT INTO user (name, fullname) VALUES (% s,% s) ('adam',' Adam Gu') COMMIT

Inserting multiple records at a time is also easy, as long as you pass a list of dictionaries (each dictionary must have the same key) to execute ().

Conn.execute (address_table.insert (), [{'user_id': 1,' email': 'sprinfall@gmail.com'}, {' user_id': 1, 'email':' sprinfall@hotmail.com'},])

The specific INSERT statement in the debugging information:

INSERT INTO address (user_id, email) VALUES (% s,% s) ((1, 'sprinfall@gmail.com'), (1,' sprinfall@hotmail.com')) COMMIT

Take a look at the inserted address in MySQL:

Mysql > select * from address +-+-- +-+ | id | user_id | email | +-+-- +-- + | 1 | 1 | sprinfall@gmail.com | | 2 | 1 | sprinfall@hotmail. Com | +-+-- +-+ 2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.