20171121_python study six weeks two lessons (November 21)

Task:

12.5 Cursors
12.6 MySQL Connection pool
12.7 Design Table Structure

Notes:

A) cursor operation

Let's first look at an example:

Next, we add a piece of data to the database via Python code, as follows:

Import MySQLdb

Def connect_mysql ():

Db_config = {

' Host ': ' 192.168.48.128 ',

' Port ': 3306,

' User ': ' Xiang ',

' passwd ': ' 123456 ',

' db ': ' Python ',

' CharSet ': ' UTF8 '

}

CNX = MySQLdb.connect (**db_config)

Return CNX

if __name__ = = ' __main__ ':

CNX = Connect_mysql ()

cus = Cnx.cursor ()

sql = "CREATE TABLE test (ID int not null); INSERT into test (ID) values (100);

Try

Cus.execute (SQL)

Cus.close ()

Cnx.commit ()

Except Exception as E:

Cnx.rollback ()

Print (' Error ')

# Raise E

Finally

Cnx.close ()

Results:

Error

To view data in a database: SELECT * FROM Employees; it hasn't changed.

Explain:

When we insert data hire time field: hire_date, deliberately write the time incorrectly, causing the exception to occur, after catching the exception, print error, and finally close the MySQL connection. cus = Cnx.cursor () is the creation of a cursor object, which we introduce later.

1, creating a Cursor object

Import MySQLdb

Db_config = {

' Host ': ' 192.168.48.128 ',

' Port ': 3306,

' User ': ' Xiang ',

' passwd ': ' 123456 ',

' db ': ' Python ',

' CharSet ': ' UTF8 '

}

CNX = MySQLdb.connect (**db_config)

cus = Cnx.cursor ()

This is the creation of a cursor object, and our various operations on MySQL are based on cursors, which we'll cover in detail later.

Before we get into this approach, let's start by understanding what a cursor is,

Cursors (cursor)

A cursor is a data buffer opened by the system for the user, which holds the results of the SQL statement execution

The user can use the SQL statement to get the records from the cursor one by one, and assign the main variables to Python for further processing, a set of main variables can only hold a single record at a time

Using only the primary variable does not fully satisfy the SQL statement's requirement to output data to the application

1. Advantages of cursors and cursors

In a database, cursors are a very important concept. Cursors provide a flexible means of manipulating data retrieved from a table, essentially, a cursor is essentially a mechanism for extracting one record at a time from a result set that includes multiple data records. Cursors are always associated with an SQL selection statement because the cursor consists of a result set (which can be 0, one, or multiple records retrieved by a related selection statement) and a cursor position in the result set that points to a particular record. When you decide to process a result set, you must declare a cursor that points to the result set.

Common methods:

Cursor (): Creating a Cursor Object
Close (): Close this Cursor object
Fetchone (): Get the next row of the result set
Fetchmany ([size = Cursor.arraysize]): Gets the next few rows of the result set
Fetchall (): Get all the rows left in the result set
Excute (sql[, args]): Execute a database query or command
Executemany (sql, args): Execute multiple database queries or commands

Let's look at an example:

Import MySQLdb

Def connect_mysql ():

Db_config = {

' Host ': ' 192.168.48.128 ',

' Port ': 3306,

' User ': ' Xiang ',

' passwd ': ' 123456 ',

' db ': ' Python ',

' CharSet ': ' UTF8 '

}

CNX = MySQLdb.connect (**db_config)

Return CNX

if __name__ = = ' __main__ ':

CNX = Connect_mysql ()

cus = Cnx.cursor ()

sql = "" SELECT * fromemployees;

Try

Cus.execute (SQL)

RESULT1 = Cus.fetchone ()

Print (' RESULT1: ')

Print (RESULT1)

RESULT2 = Cus.fetchmany (1)

Print (' result2: ')

Print (RESULT2)

RESULT3 = Cus.fetchall ()

Print (' RESULT3: ')

Print (RESULT3) cus.close ()

Cnx.commit ()

Except Exception as E:

Cnx.rollback ()

Print (' ERROR ')

Raise E

Finally

Cnx.close ()

Results:

RESULT1:

(1001L, U ' Lingjiang ', U ' M ', Datetime.date (2015, 4, 1))

RESULT2:

((1002L, U ' Xiang ', u ' M ', Datetime.date (2015, 4, 1)),)

RESULT3:

((1003L, U ' shang ', U ' M ', Datetime.date (2015, 4, 1)),)

Explain:

1, first build MySQL Connection object via MySQLdb.connect (**db_config)

2, creating a cursor through = Cnx.cursor ()

3,fetchone (): A piece of data in the final search data

4,fetchmany (1) in the next data in the 1 rows of data, this number can be customized, the definition of how much is in the result set to take the number of data.

5,fetchall () is to get all the data out of all the results.

For the methods of Excute () and Excutemany (), we will analyze their differences in detail in a section.

Be aware of SQL when executing multiple statements:

#!/usr/bin/env python

#-*-Coding:utf-8-*-

# @Time: 2017/9/18 22:17

# @Author: Lingxiangxiang

# @File: domon3.py

From Demon2 import Connect_mysql

Import MySQLdb

Def connect_mysql ():

Db_config = {

"Host": "192.168.48.128",

"Port": 3306,

"User": "Xiang",

"passwd": "123456",

"DB": "Python",

"CharSet": "UTF8"

}

Try

CNX = MySQLdb.connect (**db_config)

Except Exception as E:

Raise E

Return CNX

if __name__ = = "__main__":

sql = "SELECT * from TMP;"

sql1= "INSERT into TMP (ID) value (%s);"

param = []

For I in xrange (100, 130):

Param.append ([Str (i)])

Print (param)

CNX = Connect_mysql ()

cus = Cnx.cursor ()

Print (dir (cus))

Try

Cus.execute (SQL)

Cus.executemany (SQL1, param)

# Help (Cus.executemany)

RESULT1 = Cus.fetchone ()

Print ("RESULT1")

Print (RESULT1)

RESULT2 = Cus.fetchmany (3)

Print ("Result2")

Print (RESULT2)

RESULT3 = Cus.fetchall ()

Print ("RESULT3")

Print (RESULT3)

Cus.close ()

Cnx.commit ()

Except Exception as E:

Cnx.rollback ()

Raise E

Finally

Cnx.close ()

b) Database connection pool

In Python programming, you can use MYSQLDB for database connections and operations such as query/insert/update, but each time you connect a MySQL database request, it is independent to request access, a waste of resources, and the number of visits to a certain number, the performance of MySQL will have a greater impact. Therefore, in actual use, the database connection pooling technology is usually used to access the database to achieve the purpose of resource reuse.

Python database Connection Pool package Dbutils:

Dbutils is a set of Python database connection pool packages and allows thread-safe wrapping of non-thread-safe database interfaces. Dbutils from Webware for Python.

The Dbutils offers two external interfaces:

* PERSISTENTDB: Provides a thread-specific database connection and automatically manages the connection.

* POOLEDDB: Provides a database connection that can be shared between threads and automatically manages connections.

: https://pypi.python.org/pypi/DBUtils/download and unzip, install using the Python setup.py install command

or use

Pip Install Dbutils

#!/usr/bin/env python

#-*-Coding:utf-8-*-

# @Time: 2017/9/18 22:46

# @Author: Lingxiangxiang

# @File: demon4.py

Import MySQLdb

From DBUTILS.POOLEDDB import pooleddb

Db_config = {

"Host": "192.168.48.128",

"Port": 3306,

"User": "Xiang",

"passwd": "123456",

"DB": "Python",

"CharSet": "UTF8"

}

Pool = Pooleddb (MySQLdb, 5, **db_config) # 5 is the minimum number of connections in the connection pool

conn = Pool.connection () # Every time a database connection is needed, it's OK to get a connection using the connection () function.

cur = conn.cursor ()

SQL = "SELECT * from TMP;"

R = Cur.execute (SQL)

R = Cur.fetchall ()

Print (R)

Cur.close ()

Conn.close ()

Parameters of the POOLEDDB:

1. mincached, minimum number of idle connections, if the number of idle connections is less than this, pool creates a new connection

2. maxcached, the maximum number of idle connections, if the number of idle connections is greater than this, pool closes the idle connection

3. MaxConnections, maximum number of connections,

4. Blocking, when the number of connections reaches the maximum number of connections, when the connection is requested, if this value is true, the program requesting the connection will wait until the current number of connections is less than the maximum number of connections, if this value is False, will be error,

5. Maxshared when the number of connections reaches this number, the newly requested connection will share the connection that has been allocated

In Uwsgi, each HTTP request is distributed to a process in which the number of connections configured in the connection pool is a process unit (that is, the maximum number of connections above, the number of connections in a process), and if the business The number of SQL connections required in an HTTP request is not many (most of them only need to create a connection) and the configured number of connections is not configured to be too large.

The performance improvement of the connection pool is shown in:

1. When the program creates a connection, it can be obtained from an idle connection, without having to reinitialize the connection to increase the speed of getting the connection.

2. When the connection is closed, put the connection back into the connection pool instead of the real shutdown, so you can reduce the frequent opening and closing of the connection

1. Build a table

Database key table, executed directly in Python code,

#!/usr/bin/env python

#-*-Coding:utf-8-*-

# @Time: 2017/5/5 13:56

# @Author: Lingxiangxiang

# @File: aa.py

Import MySQLdb

Def connect_mysql ():

Db_config = {

' Host ': ' 192.168.48.128 ',

' Port ': 3306,

' User ': ' Xiang ',

' passwd ': ' 123456 ',

' db ': ' Python ',

' CharSet ': ' UTF8 '

}

CNX = MySQLdb.connect (**db_config)

Return CNX

if __name__ = = ' __main__ ':

CNX = Connect_mysql ()

cus = Cnx.cursor ()

# sql = "' INSERT into student (ID, name, age, gender, score) VALUES (' 1001 ', ' Ling ', ' M ', ' '), (' 1002 ', ' ajing ', 29, ' m ', ' N ', (' 1003 ', ' Xiang ', ' m ', 87);

Student = "CREATE TABLE student (

StdID int NOT NULL,

Stdname varchar (+) NOT NULL,

Gender enum (' M ', ' F '),

Age tinyint

)‘‘‘

Course = "CREATE TABLE course (

Couid int NOT NULL,

CName varchar (NOT NULL),

TID int NOT NULL

)‘‘‘

Score = "CREATE TABLE score (

SID int NOT NULL,

StdID int NOT NULL,

CID int NOT NULL,

Grade int NOT NULL

)‘‘‘

Teacher = "CREATE TABLE teacher (

TID int NOT NULL,

Tname varchar (+) NOT NULL

)‘‘‘

TMP = ' Set @i: = 0;

CREATE TABLE tmp as SELECT (@i: = @i + 1) as ID from information_schema.tables limit 10;

‘‘‘

Try

Cus.execute (Student)

Cus.execute (Course)

Cus.execute (Score)

Cus.execute (Thearch)

Cus.execute (TMP)

Cus.close ()

Cnx.commit ()

Except Exception as E:

Cnx.rollback ()

Print (' ERROR ')

Raise E

Finally

Cnx.close ()

Results:

Mysql> Show tables;

+------------------+

| Tables_in_python |

+------------------+

| Course |

| Score |

| Student |

| Teacher |

| TMP |

+------------------+

1 rows in Set (0.00 sec)

Without any exceptions, the tables are viewed in the database, and these five tables appear. Indicates that the five tables have been created successfully.

Since we are going to make it as close as possible to the actual combat, we have to make the data a bit larger, the statement design is a bit more complex, so that we have access to the simple SQL statement, there is a great help.

First, let's look at the Information_schema library, which is available when MySQL is installed, providing access to database metadata. What is a meta-database? Metadata is data about the data, such as the database name or table name, the data type of the column, or access permissions. Some of the other terms used to express this information include the data dictionary and the system catalog.

Information_schema database Table Description:

Schemata Table: Provides information for all databases in the current MySQL instance. This table is the result of show databases.

Tables Table: Provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on, a table belongs to. This table is the result of show tables from SchemaName.

Columns table: Provides the column information in the table. Describes in detail all the columns of a table and the information for each column. This table is the result of show columns from Schemaname.tablename.

Statistics Table: Provides information about the index of a table. This table is the result of show index from Schemaname.tablename.

User_privileges (User Rights) Table: gives information about the full range of permissions. This information is derived from the Mysql.user authorization form. Non-standard tables.

Schema_privileges (Program permissions) Table: Provides information about schema (database) permissions. This information is from the Mysql.db authorization form. Non-standard tables.

Table_privileges (table permissions) Table: Gives information about table permissions. This information is derived from the Mysql.tables_priv authorization form. Non-standard tables.

Column_privileges (column permissions) Table: Gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form. Non-standard tables.

Character_sets (character Set) Table: Provides information about the available character sets for MySQL instances. This table is the show CHARACTER set result set.

Collations table: Provides cross-reference information about each character set.

Collation_character_set_applicability table: Indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of show collation.

Table_constraints table: A table describing the existence of constraints. And the constraint type of the table.

Key_column_usage table: Describes the key columns that have constraints.

Routines Table: Provides information about storing subroutines (stored programs and functions). At this point, the routines table does not contain a custom function (UDF). A column named "Mysql.proc name" indicates the corresponding INFORMATION_SCHEMA. The Mysql.proc table column for the routines table.

Views Table: Gives information about the view in the database. You need to have show views permission, otherwise you cannot view the view information.

Triggers table: Provides information about the triggering program. You must have super privileges to view the table

and tables in the installation of MySQL, there must be data, because in the initialization of MySQL, you need to create a system table, the table must have data.

Set @i: = 0;
CREATE TABLE tmp as SELECT (@i: = @i + 1) as ID from information_schema.tables limit 10;

The variables in MySQL do not have to be declared beforehand, use "@ variable name" when using it. Set this is a special usage of setting variables in MySQL, when @i needs to be used in Select, you must add:, so that a table tmp is created to view the TMP data:

Mysql> SELECT * FROM TMP;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+------+

Rows in Set (0.00 sec)

We just take 10 data from the Information_schema.tables table, any table with 10 data is also possible, and then the variable @i as the value of the ID column, 10 times continuously output, based on the results of the last Select, CREATE TABLE tmp.

20171121_python study six weeks two lessons (November 21)