Management of Python-mysql Database

One. Using MySQL

MySQL is the most widely used database server in the Web world.
MySQL has a variety of database engines inside, the most commonly used engine is the InnoDB that supports database transactions.

Two. Install MySQL
1. Be sure to set the password to ensure the security of the database;
2. The configuration file is/etc/my.cnf;
3. Change the default encoding of the database to UTF8;
4. View the encoding format: show variables like '%char% ';
5. Install the Mysqlpython, which encapsulates the MySQL C-driven Python driver.

Installing the Database

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/A4/EC/wKioL1m0oieig09TAAAbWtAlKuA138.png "style=" float : none; "title=" screenshot from 2017-09-10 10-17-51.png "alt=" Wkiol1m0oieig09taaabwtalkua138.png "/>

Installing the Python database module

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/A4/EC/wKioL1m0oieRnxmtAABP8t4NnT8124.png "style=" float : none; "title=" screenshot from 2017-09-10 10-19-21.png "alt=" Wkiol1m0oiernxmtaabp8t4nnt8124.png "/>

Set Password

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/06/3B/wKiom1m0okzyMYdwAAAt1bWscpo489.png "style=" float : none; "title=" screenshot from 2017-09-10 10-23-08.png "alt=" Wkiom1m0okzymydwaaat1bwscpo489.png "/>

Three. Establish a MySQL connection
conn = MySQLdb.connect (host= ' localhost ', \
User= ' root ', passwd= ' 123456 ', \
db= ' Testpython ', \
port=3306,charset= ' UTF8 ')

Host:mysql Database Address
User: Database login username
passwd: Database login Password
DB: The library name to be manipulated after logging into the database
Port: Database listening port, default is 3306
CharSet: Database encoding

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A4/EC/wKioL1m0o5eibEcaAAAmVQjW9L0096.png "title=" Screenshot from 2017-09-10 10-29-33.png "alt=" Wkiol1m0o5eibecaaaamvqjw9l0096.png "/>

Four. MySQL connection operation
1.commit () If the database table has been modified, commit to save the current data.
2. Rollback () If you have permission, cancel the current operation, or the error
3.cursor () cursor pointer.

Five. Cursor operations
1. Create a database cursor
650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/06/3B/wKiom1m0pObTn3mKAAAMe_oWIj8904.png "title=" Screenshot from 2017-09-10 10-34-32.png "alt=" Wkiom1m0pobtn3mkaaame_owij8904.png "/>

Cursor () provides a way to do this, mainly by:
(1) Execution of orders
(2) Receive result cursor operation

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/A4/EC/wKioL1m0pVHjKB42AAAQeIVcElw033.png "title=" Screenshot from 2017-09-10 10-36-57.png "alt=" Wkiol1m0pvhjkb42aaaqeivcelw033.png "/>

2.cursor how to execute commands:

(1) Execute (query, args): Executes a single SQL statement. Query is the SQL statement itself, and args is the list of parameter values. The return value after execution is the number of rows affected.

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/A4/EC/wKioL1m0pyKwcoUhAABGpNXf6sc735.png "title=" Screenshot from 2017-09-10 10-44-39.png "alt=" Wkiol1m0pykwcouhaabgpnxf6sc735.png "/>

(2) Executemany (query, args): Executes a single SQL statement, but repeats the parameters in the list of parameters, and the return value is the number of rows affected.

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/A4/ED/wKioL1m0s5zzKDziAAAjw0jVajs839.png "title=" Screenshot from 2017-09-10 11-37-53.png "alt=" Wkiol1m0s5zzkdziaaajw0jvajs839.png "/>

(3) Conn.commit () Commit a transaction

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/A4/ED/wKioL1m0s8igbyF0AAAMRFJuvd8919.png "title=" Screenshot from 2017-09-10 11-38-40.png "alt=" Wkiol1m0s8igbyf0aaamrfjuvd8919.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/06/3C/wKiom1m0tMDSBZg6AABAnr_XHaA490.png "title=" Screenshot from 2017-09-10 11-42-07.png "alt=" Wkiom1m0tmdsbzg6aabanr_xhaa490.png "/>

Six. Inserting data
In daily work, most of the data is inserted by stitching an SQL statement and then executing it:
sql = "INSERT into member (Username,password,email)
Values ('%s ', '%s ', '%s ') '% (' westos ', ' 123456 ', ' [email protected] ')
Cur.execute (SQL)
Conn.commit ()

Single

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/A4/ED/wKioL1m0tNHxh7p5AAAfxBpFamo434.png "title=" Screenshot from 2017-09-10 11-43-05.png "alt=" Wkiol1m0tnhxh7p5aaafxbpfamo434.png "/>

Multiple bars, setting variables

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/06/3B/wKiom1m0qIPiuKtcAAAx2IWhYjw675.png "title=" Screenshot from 2017-09-10 10-49-58.png "alt=" Wkiom1m0qipiuktcaaax2iwhyjw675.png "/>

Seven. Querying data

1.fetchone (): Returns a result row.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/06/3C/wKiom1m0tsPyqMSsAACgX6nKmwE532.png "style=" float : none; "title=" screenshot from 2017-09-10 11-47-54.png "alt=" Wkiom1m0tspyqmssaacgx6nkmwe532.png "/>

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/A4/ED/wKioL1m0tp_CVWm4AACaD-_amVs470.png "style=" float : none; "title=" screenshot from 2017-09-10 11-48-07.png "alt=" Wkiol1m0tp_cvwm4aacad-_amvs470.png "/>

2.fetchall (self): receives all the returned result rows.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/06/3C/wKiom1m0tsTBj9MVAAA77IqSftA296.png "style=" float : none; "title=" screenshot from 2017-09-10 11-49-13.png "alt=" Wkiom1m0tstbj9mvaaa77iqsfta296.png "/>

3.fetchmany (Size=none): Receives a size bar to return the result row. If the value of size is greater than the number of result rows returned, the Cursor.arraysize bar data is returned.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M02/06/3C/wKiom1m0tsSDw4FlAABdbJiqsXg152.png "style=" float : none; "title=" screenshot from 2017-09-10 11-50-39.png "alt=" Wkiom1m0tssdw4flaabdbjiqsxg152.png "/>

4.scroll (value, mode= ' relative '): Moves the pointer to a line.
If mode= ' relative ', it means moving the value bar from the current row;
If mode= ' absolute ', the value bar is moved from the first row of the result set;

Management of Python-mysql Database