What does Python do with MySQL?

MySQL database is now the highest market share of the database, in the development process, in many cases we need to operate MySQL, so the python operation of MySQL knowledge is essential.

The Python standard database interface is Python db-api, and Python Db-api provides developers with a database application programming interface.

Reference address: Https://wiki.python.org/moin/DatabaseInterfaces, you can view a detailed list of Python support databases.

Different databases need to download different DB API modules.

DB-API is a specification. It defines a set of required objects and database access methods to provide a consistent access interface for a wide variety of underlying database systems and a wide variety of database interface programs.

Python's Db-api, which implements the interface for most databases, uses it to connect to each database and manipulate the databases in the same way.

Python DB-API Usage Process:

1. Introduce the API module.

2. Get a connection to the database.

3. Execute SQL statements and stored procedures.

4. Close the database connection.

1. Install the required packages

MYSQLDB is the interface for the Python link MySQL database, which implements the Python database API specification V2.0, built on the MySQL C API.

If it is a Windows system: Login https://pypi.python.org/pypi/MySQL-python/1.2.5 Find the package at the end of the. exe;

Download the installation, and then execute it in cmd:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/20/64ae193ccae5be8f8d75eb9631a50c73.png "title=" Clipboard.png "alt=" 64ae193ccae5be8f8d75eb9631a50c73.png "/>

If the result is as shown, you have successfully installed, if you have the following error message,

>>> Import MySQLdb

Traceback (most recent):

File "<stdin>", line 1, in <module>

Importerror:no module named MySQLdb

There is a problem with the environment variable, add the path of the. exe package that you just downloaded to the environment variable.

If it is a Linux system, you can download the source package for installation:

The ZIP package in the link is then installed:

# yum Install–y python-devel

# yum Install–y mysql-devel

# yum Install–y gcc

Extract:

# Unzip Mysql-python-1.2.5.zip

# CD mysql-python-1.2.5

# python setup.py Build

# python setup.py Install

# python

>>> Import MySQLdb

2. Connection to the database

MYSQLDB provides the Connect method to establish a connection to the database, receive several parameters, and return the connection object:

The usual way to manually build a python library in MySQL's database:

> CREATE database python;

Connect to the Python library using the following code:

Conn=mysqldb.connect (host= "192.168.48.128", user= "test", passwd= "123456", db= "Python", port=3306,charset= "UTF8")

Several parameters are included:

Host: the database hostname. By default, the local host

User: Database login name. The default is the current user

PASSWD: The Secret of database landing. Default is Empty

DB: The name of the database to use. No default value

The TCP port used by the Port:mysql service. Default is 3306, number type

CharSet: Database encoding Character set

More information on the parameters can be found here http://mysql-python.sourceforge.net/MySQLdb.html

Sometimes, for code specification, it is recommended to write the configuration of all databases in a dictionary, as follows:

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

This kind of code is more standardized, even if the database has a protocol, only need to change the contents of the Db_config dictionary can be, the later content will not change, increase the portability of code;

You can also wrap the MySQL connection as a function, and then, when you connect to MySQL, call the function directly!

3.Mysql transactions

Understand what MySQL is.

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, you operate a database, one of the company's employees quit, you want to delete his data in the database, but also to delete the person related, such as mailbox, personal assets. These database manipulation languages form a single transaction. In MySQL, transactions are supported only by databases or tables that use the InnoDB database engine, so in many cases we use the InnoDB engine. Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all.

In general, transactions are required to meet 4 conditions (ACID): atomicity (atomicity), consistency (stability), isolation (isolation), durability (reliability)

1. Atomicity of a transaction: a set of transactions, either successful or withdrawn.

2, Stability: There is illegal data (foreign key constraints and the like), transaction recall.

3. Isolation: The transaction runs independently. The result of one transaction affects other transactions, and then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.

4, Reliability: After the hardware and software crashes, the INNODB data table driver will use the log file to reconstruct the changes. Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.

MySQL in the default case, is to each select,insert,update,delete and so on as a transaction, log into the MySQL server, into MySQL, execute the following command:

Mysql> Show variables like ' auto% ';

+----------------------------------+--------+| variable_name                      | value  |+----------------------------------+--------+| auto_increment_increment          | 1      | |  auto_increment_offset            | 1       | |  autocommit                        | on     | |  automatic_sp_privileges          | ON      |+----------------------------------+--------+4 rows in set  (0.00  SEC) 

As shown above:

There is a parameter autocommit is the meaning of automatic submission, each execution of a msyql select,insert,update , etc., will be automatically submitted.

If the option is closed , we can manually commit the code each time we execute it, and the Connect object provides us with two ways to manipulate the commit data.

4. Methods of MySQL Transactions

commit (): Commits the current transaction, if the database that supports the transaction does not commit or delete, the database default rollback, white operation

rollback (): cancels the current transaction

Let's take a look at the following example:

Create a table of employees:

> CREATE TABLE Employees (

Emp_no int NOT NULL auto_increment,

Emp_name varchar (+) NOT NULL,

Gender enum (' M ', ' F ') is not NULL,

Hire_date date NOT NULL,

Primary KEY (Emp_no)

);

EMP_NO: Employee ID, primary key, and unique

Emp_name: Employee's name

Fender: Gender, only M and F two options

Hire_date: Time to hire.

Insert several data:

> INSERT INTO Employees (EMP_NO, emp_name, Gender, hire_date) VALUES (1001, ' Li ', ' M ', ' 2015-04-01 ');

> INSERT INTO Employees (EMP_NO, emp_name, Gender, hire_date) VALUES (1002, ' Xian ', ' M ', ' 2015-04-01 ');

> INSERT INTO Employees (EMP_NO, emp_name, Gender, hire_date) VALUES (1003, ' Sheng ', ' M ', ' 2015-04-01 ');

> select * from Employees;

+-----------+----------------+----------+----------------+| emp_no    | emp_name        | gender   | hire_date       |+-----------+----------------+----------+----------------+|   1001     | li             |  m        | 2015-04-01     | |    1002    | xian            | M        | 2015-04-01      | |    1003    | sheng           | M        | 2015-04-01      |+-----------+----------------+----------+----------------+ 

Add a piece of data to the database using Python code, as follows:

Import mysqldbdef 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);  &nbsP;  try:        cus.execute (SQL)          cus.close ()         cnx.commit ()      except exception as e:        cnx.rollback ( )         print (' Error ')          # raise e    finally:         Cnx.close ()

Results returned:

Error

View data in the database: SELECT * FROM Employees;

And it hasn't changed.

Explain:

When we insert the 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 function of creating a cursor object.

This article is from the "Note space" blog, so be sure to keep this source http://286577399.blog.51cto.com/10467610/1983631

What does Python do with MySQL?