Shulou(Shulou.com)06/01 Report--

This article mainly introduces Python how to achieve MySQL client-side operation library, has a certain reference value, friends in need can refer to. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

PyMySQL is a pure Python implementation of MySQL client operation library, supporting transactions, stored procedures, batch execution and so on. PyMySQL follows the Python database API v2.0 specification and includes pure-Python MySQL client libraries.

Install pip install PyMySQL to create a database connection import pymysqlconnection = pymysql.connect (host='localhost', port=3306, user='root', password='root', db='demo', charset='utf8')

Parameter list:

Parameter describes the host database server address, default localhostuser user name, default is the current program running user password login password, default is empty string database default operation database port database port, default is 3306bind_address when the client has multiple network interfaces, specify the interface to connect to the host. The parameter can be a hostname or an IP address. Unix_socketunix socket address, which is different from the host connection read_timeout read data timeout (in seconds). Default unlimited write_timeout write data timeout (in seconds). Default unlimited charset database encoding sql_mode specifies the default SQL_MODEread_default_fileSpecifies my.cnf file to read these parameters from under the [client] timeout. This is used to provide custom marshalling and unmarshaling of types.use_unicodeWhether or not to default to unicode strings. This option defaults to true for Py3k.client_flagCustom flags to send to MySQL. Find potential values in constants.CLIENT.cursorclass sets the default cursor type init_command initialization SQL statement connect_timeout connection timeout executed after the connection is established, default 10, minimum 1, maximum 31536000sslA dict of arguments similar to mysql_ssl_set () 's parameters. Whether For now the capath and cipher arguments are not supported.read_default_groupGroup to read from in the configuration file.compressNot supportednamed_pipeNot supportedautocommit is automatically submitted or not is not automatically submitted by default. A parameter value of None indicates that the server shall prevail as local_infileBoolean to enable the use of LOAD DATA LOCAL command. (default: False) the maximum amount of data sent by max_allowed_packet to the server. The default is whether 16MBdefer_connect connects lazily, and the default is to connect auth_plugin_mapA dict of plugin names to a class that processes that plugin immediately. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt (echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental) server_public_keySHA256 authenticaiton plugin public key value. (default: None) aliases for db parameter database, aliases for passwd parameter password, aliases for binary_prefixAdd _ binary prefix on bytes and bytearray. (default: False) execute SQL

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

# get cursor cursor = connection.cursor () # create data table effect_row = cursor.execute (''CREATE TABLE `users` (`name` varchar (32) NOT NULL, `age`users` (10) unsigned NOT NULL DEFAULT' 0mm, 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`, `age`) VALUES (% (name) s,% (age) s)', info) connection.commit ()

Executemany (sql, args) batch execution of SQL

# get cursor cursor = connection.cursor () # batch insert effect_row = cursor.executemany ('INSERT INTO `users` (`name`, `age`) VALUES (% s,% s) ON DUPLICATE KEY UPDATE age=VALUES (age), [(' hello', 13), ('fake', 28),]) connection.commit ()

Note: INSERT, UPDATE, DELETE and other statements to modify data need to manually execute connection.commit () to complete the submission of data modification.

Get self-added IDcursor.lastrowid query data # execute query SQLcursor.execute ('SELECT * FROM `users`') # get single data cursor.fetchone () # get the first N pieces of data cursor.fetchmany (3) # get all data cursor.fetchall () cursor control

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

Cursor.scroll (1, mode='relative') # move cursor.scroll (2, mode='absolute') # relative to the current position set cursor type relative to absolute position

When querying, the data type returned by default is tuple, and you can customize the return type. 5 cursor types are supported:

Cursor: default, tuple typ

DictCursor: dictionary type

DictCursorMixin: supports custom cursor types, which need to be customized before you can use them

SSCursor: unbuffered tuple type

SSDictCursor: unbuffered dictionary type

Unbuffered cursor type, suitable for large amount of data, slow return at one time, or low server bandwidth. Source code comments:

Unbuffered Cursor, mainly useful for queries that 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 will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network

Or if the result set is very big.

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

When you create a connection, you 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 the type when you create the cursor:

Cursor= connection.cursor (cursor=pymysql.cursors.DictCursor) transaction processing

Open a transaction

Connection.begin ()

Submit changes

Connection.commit ()

Roll back the transaction

Connection.rollback ()

Prevent SQL injection

Escape special characters

Connection.escape_string (str)

Parameterized statement

Support passing parameters for automatic escape 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) info = {'name':' fake', 'age': 15} effect_row = cursor.execute (' INSERT INTO `users` (`name`, `age`) VALUES (% (name) s,% (age) s)' Info) # batch insert effect_row = cursor.executemany ('INSERT INTO `users` (`name`, `age`) VALUES (% s,% s) ON DUPLICATE KEY UPDATE age=VALUES (age), [(' hello', 13), ('fake', 28),]) Thank you for reading this article carefully Hope the editor to share Python how to achieve MySQL client operating library content is helpful to everyone, but also hope that you support a lot, pay attention to the industry information channel, encounter problems to find, detailed solutions waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.