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

Brief introduction

In any application, persistent storage is required, and there are generally three basic storage mechanisms: files, database systems, and some mixed types. This hybrid type includes API, ORM, file managers, spreadsheets, configuration files, and so on, on existing systems. Before you can understand databases and how to use them in Python, you first need to know database concepts and SQL statements.

Underlying storage

Databases usually use file systems as basic persistent storage, which can be ordinary operating system files, dedicated operating system files, or even raw disk partitions.

User interface

Most database systems provide command-line tools that you can use to execute SQL statements or queries. There are also GUI tools that use command-line clients or database client libraries to provide users with a convenient interface.

Database

A relational database management system (RDBMS) can usually manage multiple databases, such as sales, marketing, user support, etc., all on the same server.

module

Database storage can be abstracted as a table. Each row of data has fields that correspond to the columns of the database. The collection of table definitions for each row and the data type of each table are put together to define the schema of the database. Databases can be created (create) and deleted (drop), as can tables. Adding new rows to the database is called insert, modifying existing rows in the table is called update, and removing existing rows in the table is called delete. These actions are often referred to as database commands or operations. Use an optional condition request to get a row in the database called a query.

SQL

Database commands and query operations are submitted to the database through SQL statements. Although not all databases use SQL statements, most relational databases do. Here are some examples of SQL commands. Most databases are case-insensitive, but using uppercase letters for database keywords is the most widely accepted style. Most commands require a semicolon (;) at the end to end this statement.

Create a database

Mysql > CREATE DATABASE test;Query OK, 1 row affected (0.00 sec)

Using databases and deleting databases

Mysql > USE test;Database changedmysql > DROP DATABASE test;Query OK, 0 rows affected (0.00 sec)

Create a tabl

Mysql > CREATE TABLE users (login VARCHAR (8), userid INT,projid INT); Query OK, 0 rows affected (0.02 sec)

Insert Row

Mysql > INSERT INTO users VALUES ('lena',211,1); Query OK, 1 row affected (0.00 sec)

Update Row

Mysql > UPDATE users SET userid=311 WHERE projid=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

Delete Row

Mysql > DELETE FROM users WHERE projid=1;Query OK, 1 row affected (0.00 sec)

Delete the table and empty the data

Mysql > DROP TABLE users;Query OK, 0 rows affected (0.00 sec)

In Python, the database is accessed through the adapter. The adapter is a Python module that can be used to connect to the client interface of a relational database. The figure shows the structure of writing Python database applications, including the use and absence of ORM. You can see from the figure that DB-API is the C language interface that connects to the database client.

DB-API of Python

DB-API is a standard that clarifies a range of required objects and database access mechanisms, providing consistent access to different database adapters and underlying databases. The DB-API standard requires that the functions and attributes of the following table be provided.

Attribute

Description

Apilevel requires adapter compatible DB-API version threadsafety thread safety level of this module paramstyle this module's SQL statement parameter style connect () Connect () function (multiple exceptions)

Abnormal

Data attribute

Apilevel, the string fatal module requires a compatible maximum version of DB-API

Threadsafety, indicating the security level of the module thread

0: thread safety is not supported and modules cannot be shared between threads.

1: minimize thread safety support, modules can be shared between threads, but connections cannot be shared.

2: moderate thread safety support, modules and connections can be shared between threads, but cursors cannot be shared.

3: full thread safety support, threads can share modules, connections and cursors.

Parameter style

DB-API supports specifying in different ways how to integrate parameters with SQL statements and eventually pass them to the server for execution. This parameter is a character that specifies the string alternative form to use when building a query line or command.

Example parameter style description numeric numeric location style WHERE name=:1named naming style WHERE name=:namepyformatPython dictionary printf () format conversion WHERE name=% (name) sqmark question mark style WHERE name=?formatANSIC printf () format conversion WHERE name=%s

Function attribute

The connect () function accesses the database through the Connection object. The compatibility module continues to implement the connect () function, which creates and returns a Connection object. The connect () function can pass database connection information using a string that contains multiple parameters, each parameter by location, or in the form of keyword arguments.

The parameter connect (host = 'localhost', user =' root', passwd = '123456) describes the host hostname user username passwd password db

Database name charset

Character set

Abnormal

Exception description Warning warning exception base class Error error exception base class InterfaceError data interface error DatabaseError

Database error DataError

Error OperationError occurred while processing data

An error occurred during the execution of the database operation IntegrityError database relational integrity error InternalError

Database internal error ProgrammingErrorSQL command execution failed NotSupportedError

An unsupported operation occurred

Connection object

The communication between the application and the data needs to establish a database connection. It is the most basic mechanism that can pass commands to the server and get the returned results only through a database connection. When a connection is established, you can create a cursor, send a request to the database, and then receive a response from the database.

The Connection object does not need to contain any data, but several subscript methods should be defined:

Method name describes close () closes database connection commit () commits current transaction rollback () cancels current transaction cursor () uses this link to create a cursor or cursor-like object errorhandler (cxn,sur,errcls,errval) as a handler for a cursor for a given connection

Cursor object

When a connection is established, you can communicate with the database. Cursors allow users to submit database commands and get query result rows. Python DB-API cursor objects always provide the functions of cursors, and the most important properties of cursor objects are the execute () and fetch () methods, through which all service requests against the database are executed.

Object properties describe the number of result rows fetched at a time when arraysize uses the fetchmany () method. By default, 1connection creates the connection description of the secondary cursor.

Returns the cursor activity status lastrowid

The number of rows last modified by IDrowcount last processed or affected by the execute () method callproc (func [, args])

Call the stored procedure close ()

Close the cursor execute (op [, args])

Execute a database query or command executemany (op,args)

A combination of execute () and map () that prepares and executes a database query or the command fetchone () for all given parameters

Get the next line fetchmany ([size=cursor,arraysize]) of the query result

Get the next size line fetchall () of the query result

Get all remaining lines of the query result _ _ iter__ () create an iterator object messages for the cursor

List of messages next () obtained from the database after the cursor is executed

The next line nextset () that is used by the iterator to get the query result

Move to the next result set rownumber

Index setinputsizes (sizes) of cursors in the current result set

Set the maximum allowed input size setoutputsize (size [, col])

Sets the maximum buffer size to be obtained

ORM and SQLAlchemy

The function of ORM (Object-Relational Mapping, object-relational mapping) is to make a mapping between relational database and business entity objects, so that developers do not need to deal with complex SQL statements when manipulating the data of the database, but only need to simply manipulate the properties and methods of the object. All ORM must have three basic capabilities: mapping technology, CURD operation and caching technology.

ORM establishes an intermediate layer between the card issuer and the database, which converts the data in the database into object entities in Python, which not only shields the differences between different databases, but also makes it very convenient for developers to operate the data in the database. At present, SQLAlchemy is the most mature ORM framework in Python, with rich resources and documents. It is well supported by most Python Web frameworks.

Dialect is used to connect with the data API and call different database API according to the configuration file, thus realizing the operation of the database:

MySQL-Python

Mysql+mysqldb://:@ [:] /

Pymysql

Mysql+pymysql://:@/ [?]

MySQL-Connector

Mysql+mysqlconnector://:@ [:] /

Cx_Oracle

Oracle+cx_oracle://user:pass@host:port/dbname [? key=value&key=value...]

Connect to the database:

In [1]: from sqlalchemy import create_engineIn [2]: engine = create_engine ('mysql+mysqlconnector://root@127.0.0.1:3306...: / test',echo=True)

Create a table:

In [3]: from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,Seque.: nceIn [4]: metadata = MetaData () In [5]: users = Table ('users', metadata,...: Column (' id', Integer, Sequence ('user_id_seq'), primary_key=True),...: Column (' name', String (50)), Column ('fullname', String (50)) ...: Column ('password', String (12))...:) In [6]: addresses = Table (' addresses', metadata,...: Column ('id', Integer, primary_key=True),...: Column (' user_id', None, ForeignKey ('users.id')),...: Column (' email_address', String (50)) Nullable=False):) In [7]: metadata.create_all (engine) 2017-05-19 17 metadata.create_all engine 5958 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'2017-05-19 17 metadata.create_all 59 engine 46959 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 17 Vera 59V 46960 INFO sqlalchemy.engine.base.Engine SELECT DATABASE () 2017-05-19 175959 engine 46960 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 17:59: 46962 INFO sqlalchemy.engine.base.Engine SELECT CAST ('test plain returns' AS CHAR (60)) AS anon_12017-05-19 17 AS anon_12017 59 INFO sqlalchemy.engine.base.Engine 46962 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 17 AS anon_12017 59 INFO sqlalchemy.engine.base.Engine 46963 INFO sqlalchemy.engine.base.Engine SELECT CAST (' test unicode returns' AS CHAR (60)) AS anon_12017-05-19 17 Vera 59 INFO sqlalchemy.engine.base.Engine 46963 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 1759 INFO sqlalchemy.engine.base.Engine 46964 `Uss`2017-05-19 17 INFO sqlalchemy.engine.base.Engine 59 INFO sqlalchemy.engine.base.Engine ROLLBACK2017 46964 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 17 Virtue 59 INFO sqlalchemy.engine.base.Engine ROLLBACK2017 46965 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`2017-05-19 17 INFO sqlalchemy.engine.base.Engine 59 INFO sqlalchemy.engine.base.Engine ROLLBACK2017-05-19 17V 59V 46966 INFO sqlalchemy.engine.base.Engine CREATE TABLE addresses (id INTEGER NOT NULL AUTO_INCREMENT) User_id INTEGER, email_address VARCHAR (50) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users (id)) 2017-05-19 17 NOT NULL 59 PRIMARY KEY 46967 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 17 NOT NULL 59 PRIMARY KEY 46994

Insert data:

In [8]: ins = users.insert () In [9]: str (ins) Out [9]: 'INSERT INTO users (id, name, fullname, password) VALUES (: id,: name,: fullname,: password)' In [10]: ins = users.insert (). Values (id=1,name='jack', fullname='Jack Jones') In [11]: ins.compile (). Params Out [11]: {'fullname':' Jack Jones', 'id': 1 'name':' jack'} In [12]: conn = engine.connect () In [13]: result = conn.execute (ins) 2017-05-19 1804conn 29982 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name, fullname) VALUES (% (id) s,% (name) s,% (fullname) s) 2017-05-19 1804conn 29982 INFO sqlalchemy.engine.base.Engine {'id': 1,' name': 'jack' 'fullname':' Jack Jones'} 2017-05-19 18 INFO sqlalchemy.engine.base.Engine COMMITIn 04Switzerland 29982 INFO sqlalchemy.engine.base.Engine COMMITIn [16]: conn.execute (addresses.insert (), [more than # statements inserted.:. {'user_id': 1,' email_address': 'jack@yahoo.com'},.:. {' user_id': 1 'email_address':' jack@msn.com'},.:. {'user_id': 2,' email_address': 'www@www.org'},.:. {' user_id': 2, 'email_address':' wendy@aol.com'} ...]) 2017-05-19 18 email_address 07 VALUES 29203 INFO sqlalchemy.engine.base.Engine INSERT INTO addresses (user_id, email_address) VALUES (% (user_id) s,% (email_address) s) 2017-05-19 18 email_address 07 email_address 29203 INFO sqlalchemy.engine.base.Engine ({'user_id': 1,' email_address': 'jack@yahoo.com'}, {' user_id': 1) 'email_address':' jack@msn.com'}, {'user_id': 2,' email_address': 'www@www.org'}, {' user_id': 2, 'email_address':' wendy@aol.com'}) 2017-05-19 18 user_id': 07 user_id': 29204 INFO sqlalchemy.engine.base.Engine COMMIT Out [16]:

Query

In [17]: from sqlalchemy.sql import selectIn [18]: s = select ([users]) In [19]: result = conn.execute (s) 2017-05-19 18 In 08select 59639 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password FROM users2017-05-19 188select 59639 INFO sqlalchemy.engine.base.Engine {} In [20]: for row in result:...: print (row).: (1, 'jack') 'Jack Jones', None) In [22]: for row in conn.execute (select ([users, addresses])): more than # queries.: print (row).: 2017-05-19 18 purse 11 purl 41681 INFO sqlalchemy.engine.base.Engine SELECT users.id, users.name, users.fullname, users.password, addresses.id, addresses.user_id, addresses.email_address FROM users Addresses2017-05-19 18 Jack Jones', None 11 jack', 41681 INFO sqlalchemy.engine.base.Engine {} (1, 'jack',' Jack Jones', None, 1, 1, 'jack@yahoo.com') (1,' jack', 'Jack Jones', None, 2, 1,' jack@msn.com') (1, 'jack',' Jack Jones', None, 3, 2, 'www@www.org') (1,' jack', 'Jack Jones', None, 4,2) 'wendy@aol.com')

Update

In [27]: stmt = users.update (). Values (fullname= "Fullname:" + users.c.name) In [28]: conn.execute (stmt) 2017-05-19 18 concat 27users.update 33489 INFO sqlalchemy.engine.base.Engine UPDATE users SET fullname= (concat (% (name_1) s, users.name)) 2017-05-19 18 concat 2733 489 INFO sqlalchemy.engine.base.Engine {'name_1':' Fullname:'} 2017-05-19 18 Suzhou 2733490 INFO sqlalchemy.engine.base.Engine COMMITOut [28]:

Delete

In [31]: conn.execute (addresses.delete ()) 2017-05-19 18 addresses.delete 30 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses2017 02296 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses2017-05-19 1830 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses2017 02296 INFO sqlalchemy.engine.base.Engine {} 2017-05-19 1830 INFO sqlalchemy.engine.base.Engine DELETE FROM addresses2017 02297 INFO sqlalchemy.engine.base.Engine COMMIT Out [31]: In [32]: conn.execute (users.delete (). Where (users.c.name >'m')) 2017-05-19 1830 INFO sqlalchemy .engine.base.Engine DELETE FROM users WHERE users.name >% (name_1) s2017-05-19 18 name_1': 3015 INFO sqlalchemy.engine.base.Engine {'name_1': 'm'} 2017-05-19 18 15 15 INFO sqlalchemy.engine.base.Engine COMMIT Out [32]:

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.