Python mysqldb Pymsql

Reference Document HTTPS://WWW.PYTHON.ORG/DEV/PEPS/PEP-0249/#nextset

The content of this section

• MySQLdb
• Pymysql

MySQLdb and Pymysql respectively for Python2 and Python3 to operate the MySQL database module, the usage of the two modules is basically the same, here put two modules together how to use Python to operate the MySQL database.

One. Use of MySQLdb

1. Import the module

2. Establish a connection to get the connection object

When you make a database connection, you can receive multiple parameters in the Connect function, which is returned as a connection object

The connect parameter is described below ↓

"""Create a connection to the database. It is strongly recommendedthat your only use keyword parameters. Consult the MySQL C apidocumentation for more information.host string, host to connect user string, user to connect ASP ASSWD string, password to usedb string, database to Useport Integer, TCP/IP port to connect tounix_socket string, Loca tion of Unix_socket to Useconv conversion dictionary, see Mysqldb.convertersconnect_timeout Number of seconds to wait be Fore the connection attempt fails.compress if set, compression is enablednamed_pipe if set, a named pipe are used to con Nect (Windows only) Init_command command which is run once the connection are Createdread_default_file file from which def Ault client values is Readread_default_group configuration group to use from the default Filecursorclass class object, Used to create cursors (keyword only) use_unicode If True, text-like columns is returned as Unicode objects using the CO  Nnection ' s character set. Otherwise, Text-liKE columns is returned as strings. Columns is returned as normal strings.  Unicode objects'll always be encoded to the connection's character set regardless of this setting.charset If supplied, The connection character set is changed to this character set (MySQL-4.1 and newer).  This implies Use_unicode=true.sql_mode If supplied, the session SQL mode would be changed to this setting (MySQL-4.1 and Newer).  For more details and legal values, see the MySQL documentation. Client_flag integer, flags to use or 0 (see MySQL Docs or constants/clients.py) SSL dictionary or mapping, contains SSL  Connection parameters;  See the MySQL documentation for more details (Mysql_ssl_set ()). If this was set, and the client does not support SSL, Notsupportederror would be raised.local_infile integer, Non-zero ena Bles LOAD LOCAL INFILE; Zero Disablesthere is a number of undocumented, non-standard methods. See thedocumentation for the MySQL C API for some hints on what they do."""

View Code

conn = MySQLdb.connect (host= ' 192.168.10.165 ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' center ')     # Establish a connection to the database

3. Create a cursor to perform database operations

the data type returned by the cursor by default is tuple, when added Cursorclass=mysqldb.cursors.dictcursor, the returned data type is Dict

cursor = conn.cursor (cursorclass=mysqldb.cursors.dictcursor)

4. Using cursor objects for database operations

Data operations are mainly divided into Execute SQL and return data

Here's how the cursor is used to execute the command :

Callproc (self, procname, args)    # used to execute a stored procedure, the parameter received is a stored procedure name and a parameter list, the return value is the number of rows affected by execute (self, query, args)        # executes a single SQL statement, The parameters received are the SQL statement itself and the parameter list used, the return value is the number of rows affected executemany (self, query, args)    # executes a single SQL statement, but repeats the parameters in the list of parameters, the return value is the number of rows affected Nextset ( Self                     

Executemany processing too many commands is not necessarily good, because the data is passed along to the server side, may cause buffer overflow on the server side, and the amount of data at a time is too large, it may cause some unexpected trouble. Reasonable, batch executemany is a good way.

The following method is used by the cursor to receive the return value:

Fetchall (self)      # receives all the returned result rows. Fetchmany (self, Size=none)      # receives a size bar that returns the result row. If the value of size is greater than the number of result rows returned, The cursor.arraysize data is returned. Fetchone (self)      # Returns a result row. Scroll (self, value, mode= ' relative ')        

Let's look at a complete example:

#!/usr/bin/env python#-*-coding:utf-8-*-__author__ = ' 40kuai ' Import mysqldb  # import Module conn = MySQLdb.connect (host= ' 192.168.10.165 ', port=3306, user= ' root ', passwd= ' 123456 ', db= ' db ')     # and database established connect cursor = Conn.cursor ()  # Get cursor for data operation # query Effect_row = Cursor.executemany ("SELECT * from User limit%s", (Ten))  # Returns the number of rows affected print Effect_row # returned as affected The number of bars print cursor.fetchone ()     # prints a return result print '. Center (#, ' # ') print Cursor.fetchall ()     # Returns all the data, Here for the remaining data cursor.scroll (0,mode= ' absolute ')        # resets the cursor position print cursor.fetchone () # commits, or the newly created or modified data cannot be saved. Here for the query operation can not write Conn.commit () # Close Cursor cursor.close () # Close connection Conn.close ()

# output (approximate)
# 10
# (10001L, ' 40kuai ', 1476858893L)
# ##################################################
# ((10002L, ' 41kuai ', 1476858893L),......)  # behind data omitted
# (10001L, ' 40kuai ', 1476858893L)

5. Things to keep in mind when working with databases:

• Use SQL statements where the parameters to be received are in% s placeholder. Note that   no matter what type of data you want to insert, the placeholder will always be%s 
• param should be tuple or list 
• When adding multiple data using Executemany, the collection of each incoming parameter is a tuple, the entire parameter consists of a tuple, or the list
• Return data for dictionary type can be passed in when creating a database connection (connection) cursorclass= MySQLdb.cursors.DictCursor, or when you create a cursor (cursor), add a parameter cursorclass=mysqldb.cursors.dictcursor
• --added Cursorclass=mysqldb.cursors.dictcursor in conntction error (' module ' object has no attribute ' cursors '), there should be no cursors method with objects that have not yet been instantiated.

6. Some finishing touches after querying your data

Two functions,commit and rollback , need to be explained when manipulating the data

If a transaction-enabled storage engine is used, commit is required after each operation, otherwise it is not actually written to the database (the query operation can be executed without a commit), and the corresponding rollback can be rolled back, but the commit is no longer rollback. Commit () can be called again after executing many SQL instructions, which can improve performance appropriately.

5. Some finishing touches after querying your data

5. Some finishing touches after querying your data

Python mysqldb Pymsql