Share a pure Python implementation of the MySQL client operations Library

Pymysql is a pure Python implementation of the MySQL client operations library, support transactions, stored procedures, batch execution and so on. Pymysql follows the Python database API v2.0 specification and includes the Pure-python MySQL client library.

Installation

Pip Install Pymysql

Create a database connection

Import pymysqlconnection = pymysql.connect (host= ' localhost ',                             port=3306,                             user= ' root ',                             password= ' root ',                             db= ' demo ',                             charset= ' UTF8 ')

Parameter list:

Parameters	Description
Host	Database server address, default localhost
User	User name, defaults to the current program running user
Password	Login password, default is an empty string
Database	Database of default Actions
Port	Database port, default is 3306
Bind_address	When the client has more than one network interface, specify the interface to connect to the host. The parameter can be a host name or an IP address.
Unix_socket	UNIX socket address, different from host connection
Read_timeout	Read data time-out, per second, default unlimited
Write_timeout	Write data timeout, unit seconds, Default unlimited
CharSet	Database encoding
Sql_mode	Specify the default Sql_mode
Read_default_file	Specifies MY.CNF file to read these parameters from under the [Client] section.
Conv	Conversion dictionary to use instead of the default one. This was used to provide custom marshalling and unmarshaling of types.
Use_unicode	Whether or not to the default to Unicode strings. This option defaults to True for py3k.
Client_flag	Custom flags to send to MySQL. Find potential values in constants. CLIENT.
Cursorclass	Set the default cursor type
Init_command	An initialization SQL statement that executes after the connection is established
Connect_timeout	Connection time-out, default 10, Min 1, max 31536000
Ssl	A dict of arguments similar to Mysql_ssl_set () ' s parameters. For now the Capath and cipher arguments is not supported.
Read_default_group	Group to read from in the configuration file.
Compress	Not supported
Named_pipe	Not supported
Autocommit	Whether to submit automatically, default is not automatically committed, the parameter value is None to indicate that the server will prevail
Local_infile	Boolean to enable the use of the LOAD DATA LOCAL command. (Default:false)
Max_allowed_packet	The maximum amount of data sent to the server, which defaults to 16MB
Defer_connect	is an inert connection, the default is immediate connection
Auth_plugin_map	A dict of plugin names to a class this processes that plugin. The class would take the Connection object as the argument to the constructor. The class needs an authenticate method taking a authentication packet as an argument. For the dialog plugin, a prompt (echo, prompt) method can is used (if no Authenticate method) for returning a string from T He user. (experimental)
Server_public_key	SHA256 Authenticaiton Plugin Public key value. (Default:none)
Db	Alias for parameter database
passwd	Alias for parameter password
Binary_prefix	Add _binary prefix on bytes and bytearray. (Default:false)

Execute SQL

• Cursor.execute (SQL, args) executes a single SQL

  # gets cursor cursor = connection.cursor () # Create data table Effect_row = Cursor.execute (' Create TABLE ' users ' (' name ' varchar (+) NOT NULL, ' age ' int (unsigned) ' is not null DEFAULT ' 0 ', PRIMARY KEY (' name ')) engine= InnoDB DEFAULT Charset=utf8 ") # Insert data (tuple or list) Effect_row = Cursor.execute (' INSERT INTO ' users ' (' name ', ' age ') VALUES (%s,% s) ', (' Mary ', 18) # Insert data (dictionary) info = {' name ': ' Fake ', ' age ': 15}effect_row = Cursor.execute (' INSERT INTO ' users ' (' name ', ' a GE ') VALUES (% (name) s,% (age) s) ', info) connection.commit () 

• executemany (sql, args) bulk Execute SQL

  # gets cursors cursor = connection.cursor () # BULK Insert Effect_row = Cursor.executemany (' INSERT INTO ' users ' (' name ', ' age ') VALUES ( %s,%s) on DUPLICATE KEY UPDATE age=values, [(' Hello ', '), (' Fake ', '),]) Connection.commit () 
   

Note: Statements that modify data such as INSERT, UPDATE, DELETE, and so on, need to be manually executed connection.commit() to complete the submission of data modifications.

Get the self-increment ID

Cursor.lastrowid

Querying data

# Execute Query Sqlcursor.execute (' SELECT * from ' users ') # get a single piece of Data Cursor.fetchone () # get top N Data cursor.fetchmany (3) # Get all Data Cursor.fetchall ()

Cursor control

All data query operations are based on cursors, and we can cursor.scroll(num, mode) control the position of the cursor.

Cursor.scroll (1, mode= ' relative ') # moves relative to the current position cursor.scroll (2, mode= ' absolute ') # relative absolute position movement

Set Cursor type

When querying, the data type returned by default is a tuple and can be customized to set the return type. Supports 5 types of cursors:

• Cursor: Default, tuple type

• Dictcursor: Dictionary Type

• Dictcursormixin: Supports custom cursor types that need to be customized before they can be used

• Sscursor: unbuffered tuple type

• Ssdictcursor: No buffer dictionary type

unbuffered cursor type, suitable for large amounts of data, one-time return is too slow, or the service side bandwidth is small. SOURCE Comment:

unbuffered Cursor, mainly useful for queries so return a lot of data, or for connections to remote servers over a Slow network.

Instead of copying every row of data into a buffer, this would fetch rows as needed. The upside of the client uses much less memory, and rows was returned much faster when traveling over a slow netwo Rk
Or if the result set is very big.

There is limitations, though. The MySQL protocol doesn ' t support returning the total number of rows, so the-the-only-to-tell-how many rows there was is To iterate over every row returned. Also, it currently isn ' t possible to scroll backwards, as-is-the-current-row is-held in memory.

When creating a connection, specify the type through the Cursorclass parameter:

Connection = pymysql.connect (host= ' localhost ',                             user= ' root ',                             password= ' root ', db=                             ' demo ',                             charset= ' UTF8 ',                             cursorclass=pymysql.cursors.dictcursor)

You can also specify a type when creating a cursor:

cursor = Connection.cursor (cursor=pymysql.cursors.dictcursor)

Transaction processing

• Open transaction

connection.begin()

• Submit Changes

connection.commit()

• Rolling back a transaction

connection.rollback()

Anti-SQL Injection

• Escape special Characters
  connection.escape_string(str)

• parameterized statements
  Support incoming parameters for automatic escaping and formatting SQL statements to avoid security issues such as SQL injection.

# Insert data (tuple or list) Effect_row = Cursor.execute (' INSERT INTO ' users ' (' name ', ' age ') VALUES (%s,%s) ', (' Mary ', 18) # Insert data (dictionary) INF o = {' name ': ' Fake ', ' age ': 15}effect_row = Cursor.execute (' INSERT to ' users ' (' name ', ' age ') VALUES (% (name) s,% (age) s) ', info) # BULK Insert Effect_row = cursor.executemany (    ' insert INTO ' users ' (' name ', ' age ') VALUES (%s,%s) on DUPLICATE KEY U PDATE age=values (age) ', [(' Hello ', ' + '),        (' fake ', '),    ])

Resources

• The Pymysql module in Python that operates MySQL

• The use of Python's pymysql