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

The following is about how python manipulates mysql databases. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on how python operates the mysql database.

Basic environment: Python 3.5.1

Mysql version: 5.6.35 (rpm installation)

Operating systems: Centos7.3 and windows7

1. Introduction of python connection database module:

At present, the following drivers are mainly used, MySQLdb and pymsql, as well as mysql-connector-python drivers officially provided by mysql. MySQLdb module is more used by python2.X, while python3.X uses more pymsql. We will study the official mysql-connector-python later. This study and practice are all based on pymsql module.

The use of PyMySQL and MySQLdb is almost the same, accustomed to using MySQLdb, as long as import MySQLdb is changed to import pymysql.

Second, the method and parameters of pymysql to connect to the database:

The pymysql connection mysql uses the pymysql.connect () method, which can adjust many parameters:

Parameters.

Description

Host database address user database user name, passwd database password, default is empty db database name, no default library port database port, default 3306connect_timeout connection timeout, seconds use_unicode result returns charset insert database code as unicode string

Connection example:

Connect=pymysql.connect (host= "192.168.186.157", port=3306,user= "winner", passwd= "123123", db= "DB", charset= "utf8", connect_timeout=3000) example connection mainly contains host,user,passwrd and port parameter connection example 2:connect=pymysql.connect ("192.168.186.157", "winner", "123123", "test") without host and other parameters, but the format is fixed. The host, user, password, and initially connected database cannot be interchanged, while the above example with parameters is relatively casual. Note: the port and connection timeout here are both int, so there is no need for quotation marks

The connect () function returned by the connection object:

Commit () commits the transaction. For databases and tables that support transactions, if the commit modification operation does not apply, the rollback () transaction rollback will not be written to the database. For databases and tables that support transactions, if you execute this method, the current transaction is rolled back. Without commit (). Cursor ([cursorclass]) creates a cursor object. All sql statements are executed under the cursor object. MySQL itself does not support cursors, and the MySQLdb module simulates its cursors.

In the process of python operating mysql database, we mainly use cursor acquisition methods counect.cursor () and cursor.execute () to operate on the database, such as creating databases and data tables. We generally connect directly to the mysql client and execute SQL statements, so we do more operations such as add, delete, change, check, etc.

Cursor objects also provide several methods:

Close () closes cursor execute (sql) executes sql statement excutemany (sql) executes multiple sql statements fetchone () fetches the first record from the execution result fetchmany (n) fetches n records fetchall () fetches all records scroll (self, value, mode='relative') cursor scrolls from the execution result

Example 1. The mysql server connected to 192.168.186.157 creates a pymysql library character set of utf8

# / usr/bin/env python#_*_coding:utf-8_*_# Import pymysql module import pymysql# use pymysql.connect () method to create database link con=pymysql.connect (host='192.168.186.157',user='winner',passwd='123123',port=3306) # use con.cursor () method to create cursor cursor=con.cursor () sql= "create database If Not Exists pymysql default character set utf8 "''sql="create table if not exists class (id int (10) primary key auto_increment, name varchar (20) not null, address varchar (20) not null default" gansu ")"' 'cursor.execute (sql) cursor.execute (" show databases ") dataname=cursor.fetchall () print (dataname)

Execution result:

(('information_schema',), (' # mysql50#2017-03-16'09-38-47), ('DB',), (' mysql',), ('performance_schema',), (' pymysql',), ('test',), (' winner_mas',)) Process finished with exit code 0

Example 2. Connect the newly created pymysql database to create the class table

# / usr/bin/env python#_*_coding:utf-8_*_# Import pymysql module import pymysql# use pymysql.connect () method to create database link con=pymysql.connect (host='192.168.186.157',user='winner',passwd='123123',port=3306,db='pymysql') # use con.cursor () method to create cursor cursor=con.cursor () # sql= "create database If Not Exists pymysql default character set utf8 "sql="create table if not exists class (id int (10) primary key auto_increment, name varchar (20) not null, address varchar (20) not null default" gansu ")" cursor.execute (sql) cursor.execute ("show tables") dataname=cursor.fetchall () print (dataname) C:\ Users\ Administrator\ AppData\ Local\ Programs\ Python\ Python35\ python.exe C:/Users/Administrator/PycharmProjects/python/createdatabase.py (('class',)) ) C:\ Users\ Administrator\ AppData\ Local\ Programs\ Python\ Python35\ lib\ site-packages\ pymysql\ cursors.py:166: Warning: (1050, "Table 'class' already exists") result = self._query (query) Process finished with exit code 0

Is there anything you don't understand about the way python operates the mysql database above? Or if you want to know more about it, you can continue to follow our industry information section.

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.