Summary of reading and writing mysql using python

1. Install the MySQLdb package. in the Windows environment, download the MySQL-python-1.2.3, there are two ways :( 1)download srcsource code .tar.gz (this source code needs to build and then install. egg package (when. jar package); however, you need to modify the environment variable and MySQLdb. the configuration files in the conf folder, etc. Search for the corresponding version on the Internet.

1. Install the MySQLdb package. in win environment, download MySQL-python-1.2.3, there are two ways: (1) download srcsource code .tar.gz (this source code needs to build and then install. egg package (when. jar package); however, you need to modify the environment variable and MySQLdb. the configuration files in the conf folder, etc. Search for the corresponding version on the Internet.

1. Install the MySQLdb package

1.In a win environment, there are two ways to download a MySQL-python-1.2.3:

(1)Download srcsource code .tar.gz(This source code needs to be built and compiled before installing the. egg package (in the. jar package ));

However, after this method, you need to modify the environment variables and MySQLdb. the configuration file in the conf folder, etc. Search for the corresponding version on the Internet. The second method I used is to directly import MySQLdb In the python command line; no problem occurs.

(2)Directly download the mysql-python-1.2.3.exe installation package(Double-click to directly install );

In this example, the directly installed .exe file can be imported in the python command line, and the import in PyCharm is normal. However, when it is introduced in pydev, the system prompts "uncheck package ", it is estimated that the pydev plug-in itself has some incomplete functions, or the most easily overlooked error: Forgetting to set the environment variable or environment variable path error.

2.Solve the problem that the pydev plug-in eclipse cannot introduce the MySQLdb package: Force compilation, instead of importing the file and. egg package (when the. jar package is used) into the build path.

Solution:

In Eclipse, in windows ==> preference ==> Pydev, configure the content of the ForcedBuiltins tab of Interpreter-Python,Manually addContent: MySQLdb, set the moduleForced CompilationGo in.

If you only operate on the Content of "build path System PYTHONPATH" and import the file to the database, the problem will not be solved in any case. Be sureForced CompilationGo in !!!

2. Connect to the database

Python connects to mysql mainly using the cursor for access, which is included in MySQLdb.

Add the methods and attributes of several objects:

1.Connection Parameters:Conn = MySQLdb. connect ("localhost", "root", "123456", "testdb ")

YizhuangConn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'testdb ')
Note that port = 3306 is not specified here because the default port number of MySQLdb is 3306. You do not need to specify it. If you change it yourself, write it.

Connection parameters:

• Host, the host name of the connected database server. The default value is local host ).
• User, the user name used to connect to the database. The default value is the current user.
• Passwd, connection password, no default value.
• Database, the name of the connected database, with no default value.
• Port: Specifies the connection port of the database server. The default value is 3306.
• Conv: maps text to a Python dictionary. The default value is MySQLdb. converters. conversions.
• The type used by cursorclass and cursor (). The default value is MySQLdb. cursors. Cursor.
• Compress: enables protocol compression.
• Named_pipe is connected to a named pipe in windows.
• Init_command: Once the connection is established, a statement is specified for the database server to run.
• Read_default_file: Use the specified MySQL configuration file.
• Read_default_group: the default read group.
• Unix_socket: the socket used for connection in unix. TCP is used by default.

2.Connection object
Function of method name
Conn.Close ()Close Database
Conn. Commit ()Submit current transaction

----- Commit () must be submitted after the SQL statement is written, especially when insert, update, or delete is used; otherwise, the database does not change !!!

----- A normal query like select does not involve modifying the database. It does not matter whether it is commit ().
Conn.Rollback ()Cancel current transaction

---- Handle exceptions t Exception: it is best to add rollback ()
Conn. Cursor ()Get the cursor object of the current connection
Conn.Rrorhandler (cxn, cur, errcls, errval)As the handle to the cursor

3.Cursor object attributes and Methods
(1) method for executing commands using cursor

Callproc (SQL, procname, args) ExecutionStored ProcedureThe receiving parameter is the stored procedure name and parameter list, and the returned value is the number of affected rows.
Execute (SQL, param, args)Execute a single SQL statement and receive the param parameter. The returned value is args.Number of affected rows
Executetasks (SQL, param, args)Execute Multiple SQL statements and receive the param parameter. The returned value is args.Number of affected rows
Next ()Use the iteration object to get the next row of the result
Nextset ()/nestset (self): Move to the next result set (if supported)

Close () close the cursor

Connection creates a connection to this cursor (optional)

(2) method used by cursor to receive return values

Fetchone ()Returns a result line.

Fetchall (self)Match all remaining results
Fetchsor (size-cursor, arraysize)The following rows of matching results

Rowcount reads the number of rows in the database table.The number of rows returned or affected by the last execute () operation.

Scroll (self, value, mode = 'relative '): Move the pointer to a row. If mode = 'relative ', the value bar is moved from the current row. If mode = 'absolute', the value bar is moved from the first row of the result set.

Arraysize specifies the number of records retrieved at a time using the fetchmany () method. The default value is 1.
Discription returns the active status of the cursor, including (7 elements ):

(Name, type_code, display_size, internal_size, precision, scale, null_ OK) Where name and type_code are required.
Lastrowid: return the ID of the last update row (Optional). If the database does not support this function, None is returned.

_ Iter _ () Create an iteration object (optional, refer to next ())
Messages cursor executes the list of information returned by the database (Set of tuples)
Rownumber index of the cursor in the current result set (starting from 0 rows)
Setinput-size (sizes): set the maximum input value.
Setoutput-size (sizes [, col ])

Iii. Code

(1) mysql_1.py

Import MySQLdbtry: conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'mysql', db = 'testdb') # The default port value is 3306 cursor = conn. cursor () SQL = "insert into table1 values ('20170101', 'tomy', 23, 'M', 'U. KK. ') "cursor.exe cute (SQL) conn. commit () # submit the transaction commit () cursor.exe cute ("select * from table1") results = cursor. fetchall () for re in results: print re cursor. close () conn. close () distinct T MySQLdb. error, e: conn. rollback () # Roll Back rollback () print "MySQL Error % d: % s" % (e. args [0], e. args [1]) print e

(2) mysql_2.py

#-*-Encoding: UTF-8-*-import osimport stringimport sysimport MySQLdb # connect to the database try: conn = MySQLdb. connect (host = 'localhost', port = 3306, user = 'root', passwd = 'mysql', db = 'testdb') failed t Exception, e: print e sys. exit () # obtain the cursor object to operate cursor = conn. cursor () # create a table SQL = "create table if not exists test1 (name varchar (128) primary key, age int (4)" cursor.exe cute (SQL) print "success create table test1! "# Insert data SQL =" insert into test1 (name, age) values ('% s',' % D') "% (" denny ", 23) try: cursor.exe cute (SQL) conn. commit () # All insert, update, and delete operations must "commit transaction commit ()", otherwise the database will not change print "succeess insert a record! "Cannot t Exception, e: conn. rollback () # The exception must be rolled back rollback () print e # insert multiple data SQL = "insert into test1 (name, age) values (% s, % s) "param = (" tom ", 24), (" alice ", 25), (" bob "," 26 ") try: cursor.exe cute.pdf (SQL, param) when batch data is inserted, use .executemany(sqlsyntax but not .exe cute (SQL). That is a single data record conn. commit () print "success insert into records! "Cannot t Exception, e: conn. rollback () print e # query data SQL = "select * from test1" cursor.exe cute (SQL) alldata = cursor. fetchall () # cyclically output if any data is returned. alldata is a two-dimensional list.) if alldata: for rec in alldata: print rec [0], rec [1] # output data separately, the result is denny 23 # print rec #. You can also directly output rec without the subscript rec [I]. The result is ('denny ', 23L) # Close the connection to cursor. close () conn. close ()

Console output result:

SQL table results:

(3) mysql_3.py (fetchone () example)

import MySQLdbdb = MySQLdb.connect("localhost", "root", "mysql", "testdb" )cursor = db.cursor()cursor.execute("select * from test1")data = cursor.fetchone()while data!=None:    print data[1]    data = cursor.fetchone()db.close

(4) mysql_4.py (fetchall () example)

import MySQLdbdb = MySQLdb.connect("localhost", "root", "mysql", "testdb" )cursor = db.cursor()cursor.execute("select * from test1")datas = cursor.fetchall()for data in datas:    print data[1]print cursor.rowcount,"rows in tatal"db.close