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

This article mainly introduces what the Python standard library MySQL workflow is like, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the Python standard library MySQL workflow is what it is.

The MySQLdb workflow is as follows:

Connection

The connection method is used to create a network connection between the client and the database.

Syntax:

MySQLdb.Connect (parameter)

Parameters.

Parameter type description host string MySQL CVM address port integer MySQL CVM port number user string MySQL database user name passwd string MySQL database password db string MySQL database name charset character set used for string connection

For example:

# Import MySQLdb module > import MySQLdb# create a Connect connection > conn = MySQLdb.Connect (host='127.0.0.1', user='root', passwd='as', db='USER', port=3306, charset= "utf8") > cursor = conn.cursor () > print (cursor) > print (conn) # close the connection > conn.close () > print (conn)

Methods supported by connection object

The method name indicates that cursor () uses the connection to create and return the cursor commit () commit the current transaction rollback () rollback the current transaction close () close the connection cursor

Cursor users execute queries and obtain results. The execution process is as follows:

Methods supported by the cursor object

The parameter name indicates that the SQL statement fefchone () executed by execute ("SQL") gets the next line of the result fefchmany (size) gets the next few lines of the result fefchall () gets all the remaining rows rowcount the last number of rows of data returned by execute or the number of rows affected by close () closes the cursor object transaction

A program execution unit that updates a database. An execution unit refers to a collection of operations, each of which is used to access an update database.

Atomicity: many operations included in a transaction are either done or not done at all

For example, when a user transfers money from a bank to a user B, he or she transfers money to user B, either doing both of these operations, or not doing both.

Consistency: the transaction must change the database from a consistency state to another consistency state

Isolation: the execution of one transaction cannot be interfered with by other transactions

Persistence: once a transaction is committed, its changes to the database are permanent

How do I use transactions in development?

Disable automatic commit: set conn.autocommit (False). MySQLdb is already False by default.

Normal end of transaction: conn.commit ()

Abnormal end of transaction: conn.rollback ()

Example

SELECT query data

First create a user table:

CREATE DATABASE USER;USE USER;CREATE TABLE `user` (`userid` INT (11) NOT NULL AUTO_INCREMENT, `username` VARCHAR (100th) DEFAULT NULL,PRIMARY KEY (`userid`) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

Insert the following

INSERT INTO user (userid, username) VALUES (1, 'name1'); INSERT INTO user (userid, username) VALUES (2,' name2'); INSERT INTO user (userid, username) VALUES (3, 'name3'); INSERT INTO user (userid, username) VALUES (4,' name4'); INSERT INTO user (userid, username) VALUES (5, 'name5')

View data

Mysql > SELECT * FROM user +-+-+ | userid | username | +-+-+ | 1 | name1 | | 2 | name2 | 3 | name3 | 4 | name4 | | 5 | name5 | +-+ + 5 rows in set (0.00 sec) > import MySQLdb > conn = MySQLdb.Connect (host='127.0.0.1') User='root', passwd='as', db='USER', port=3306, charset= "utf8") > cursor = conn.cursor () > SQL = "SELECT * FROM user" # returns the number of rows obtained by cursor.execute (SQL) output > print (cursor.rowcount) returns the first piece of data > > cursor.fetchone () (1, 'name1') # returns two pieces of data > cursor.fetchmany (2) ((2,' name2'), (3) 'name3')) # returns all remaining data > cursor.fetchall () ((4,' name4'), (5, 'name5'))

Insert/update/delete

Flow chart:

> import MySQLdb > conn = MySQLdb.Connect (host='127.0.0.1', user='root', passwd='as', db='USER', port=3306, charset= "utf8") > cursor = conn.cursor () > cursor.execute ("INSERT INTO user (userid, username) VALUES (50, 'name50')") 1 > > cursor.execute ("UPDATE user SET username='as' WHERE userid=1") 1 > > cursor.execute ("DELETE FROM user WHERE userid=2") 1 > > conn.commit () > > cursor.close () > > conn.close ()

View the contents of the database table

Mysql > SELECT * FROM user;+-+-+ | userid | username | +-+-+ | 1 | as | 3 | name3 | 4 | name4 | 5 | name5 | | 50 | name50 | +-+-+ 5 rows in set (0.00 sec)

# Python Standard Library # Mysqldb

After reading the above about the Python standard library MySQL workflow, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.