Connect MySQLdb database to MySQL database

PythonDB-API is a Python database application interface that supports mainstream databases such as Oracle, MySQL, DB2, MSSQL, and Sybase. However, different databases need to download different modules, such: the MySQLdb module supports MySQL. although the modulo is different, the execution steps of all these APIs are the same: 1. import API Model 2. obtain the connection to the database

Python DB-API is Python database application interface, support including Oracle, MySQL, DB2, MSSQL, Sybase and other mainstream databases, but different databases, need to download different modules, such: the MySQLdb module supports MySQL. although the modulo is different, the execution steps of all these APIs are the same: 1. import API Model 2. obtain the connection to the database

Python DB-API is Python database application interface, support including Oracle, MySQL, DB2, MSSQL, Sybase and other mainstream databases, but different databases, need to download different modules, such: the MySQLdb module supports MySQL. although the modulo is different, the execution steps of all these APIs are the same:

1. Import the API Model
2. Obtain the connection to the database.
3. Issue SQL statements and stored procedures.
4. Close the connection

The following describes the MySQLdb module:

Install

Installation is required first. Windows download: http://www.codegood.com/archives/4

Insert

# Import related modules import MySQLdb # establish a connection with the mysql database dbconn = MySQLdb. connect (host = '2017. 0.0.1 ', user = 'root', passwd = 'abcd') # obtain the cursor = dbconn.cursor()#sqlsqlsql, create a data library cursor.exe cute ("drop database if exists test" Export cursor.exe cute ("create database test ") # select the database to connect to dbconn. select_db ('test') # introduce exception handling. try: Execute SQL in sequence and create a table named cursor.exe cute ("create table log (id int, message varchar (50)") # insert a record value = [0, "Log Information ID is: 0"] curso R.exe cute ("insert into log values (% s, % s)", value) # insert multiple records values = [] for I in range (1, 11): values. append (I, 'Log Information ID is: '+ str( I ?##cursor.exe cute.pdf ("insert into Log values (% s, % s)", values) # submit to modify dbconn. commit () commit T: # Roll back if an SQL statement is incorrect! Dbconn. rollback () # close the cursor connection and release the resource cursor. close () # close the connection dbconn. close ()

Query

After the database is connected, you can use the fetchone () method to obtain multiple values of a single record or the fetchAll method fetech from the database table.
Fetchone (): This method gets the next row of the query result set. When a result set is an object, a cursor object is returned for querying the table.
Fetchall (): This method gets all rows in the result set. If some rows have been extracted from the result set, the fetchAll () method retrieves the remaining rows in the result set.
Rowcount: This is a read-only attribute and returns the execute () method of the affected rows.

# Import module import MySQLdb # You can also put the database name in Connect () dbconn = MySQLdb. connection ('2017. 0.0.1 ', 'root', 'abcd', 'test') cursor = dbconn. cursor () count = cursor.exe cute ('select * from log') print 'Total % s records', count # Get a record, print is returned for each record as a tuples. "Only one record is obtained:" result = cursor. fetchone (); print result print 'id: % s Log Info: % s' % result # obtain five records. Note that fetchone () has been executed before (), so the cursor has pointed to the second record, that is, print all records starting from the second record. "Only five records are retrieved:" results = cursor. fetchiterator (5) for res in results: print res print "get all results:" # reset the cursor position. 0 indicates the offset. mode = absolute | relative. The default value is relative and cursor. scroll (0, mode = 'absolute ') # obtain all results = cursor. fetchall () for res in results: print res dbconn. close ()

C: \ Work \ Python \ Learn> python select. py has a total of % s records. 11 only get one record: (0L, 'Log Information ID is: 0') ID: 0 Log Info: Log Information ID is: 0. Obtain only five records: (1L, 'Log Information ID is: 1') (2L, 'Log Information ID is: 2') (3L, 'Log Information ID is: 3 ') (4L, 'Log Information ID is: 4') (5L, 'Log Information ID is: 5') Get all results :( 0L, 'Log Information ID is: 0 ') (1L, 'Log Information ID is: 1') (2L, 'Log Information ID is: 2') (3L, 'Log Information ID is: 3 ') (4L, 'Log Information ID is: 4') (5L, 'Log Information ID is: 5') (6L, 'Log Information ID is: 6') (7L, 'Log Information ID is: 7') (8L, 'Log Information ID is: 8') (9L, 'Log Information ID is: 9') (10L, 'Log Information ID is: 10 ')