Connect Python to the MySQL database

Connect Python to the MySQL database

Create Database Connection import MySQLdb
Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "sa", db = "mytable ")
　
Common parameters include
Host: The host Name of the connected database server. The default host name is localhost ).

User: Database login name. The default value is the current user.
Passwd: Password for database login. Empty by default.
Db: name of the database to be used. No default value exists.
Port: the TCP port used by the MySQL service. The default value is 3306.
The conn connection has two important methods: commit [Submit add and modify] and rollback [undo add or modify]

3. perform database operations
N=cursor.exe cute (SQL, param)
We need to use the connection object to obtain a cursor object. Next, we will use the method provided by cursor to work.
These methods include: 1. execute commands; 2. Receive returned values.
Cursor is used to execute commands:
Callproc (self, procname, args): used to execute a stored procedure. The received parameters are the stored procedure name and parameter list. The returned values are the number of affected rows.
Execute (self, query, args): executes a single SQL statement. The received parameters are the SQL statement itself and the list of parameters used. The returned values are the affected rows.
Executemany (self, query, args): executes a single-pick SQL statement, but repeats the parameters in the parameter list. The returned value is the number of affected rows.
Nextset (self): Move to the next result set
Cursor is used to receive the returned value:
Fetchall (self): receives all returned result rows.
Fetchmany (self, size = None): receives the size of returned results rows. If the size value is greater than the number of returned results rows, the returned cursor. arraysize data is returned.
Fetchone (self): returns a result line.
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.
The following code is a complete example.
# When using SQL statements, the parameters to be received here use the % s placeholder. Note that no matter what type of data you want to insert, The placeholder will always use % s
SQL = "insert into cdinfo values (% s, % s )"
# Param should be tuple or list
Param = (title, singer, imgurl, url, alpha)
# Execution. If the execution succeeds, the value of n is 1.
N=cursor.exe cute (SQL, param)
# Execute another query operation
Cursor.exe cute ("select * from cdinfo ")

# We use the fetchall method. In this way, all results returned by the query will be saved in the cds. Each result is a tuple-type data, which forms a tuple.
Cds = cursor. fetchall ()

# Because it is a tuple, you can use the result set in this way.
Print cds [0] [3]
# Or directly display the result set to see how it looks.
Print cds

# If you want to insert data in batches, do this.
SQL = "insert into cdinfo values (0, % s )"
# The set of each value is a tuple, and the entire parameter set is a tuple or list
Param = (title, singer, imgurl, url, alpha), (title2, singer2, imgurl2, url2, alpha2 ))
# Use the executeplugin method to insert data in batches. This is a really cool method!
N=cursor.exe cute.pdf (SQL, param)

Note that (or I am surprised) After performing the insert, delete, or modify operation, you need to call conn. commit using the commit () method. in this way, the data is actually stored in the database. I don't know whether it is my mysql settings. In short, if I didn't use commit at the very beginning today, the data will not be retained in the database. However, the data is indeed in the database. because the automatic number is accumulated, and the number of affected rows returned is not 0.

4. Close the database connection
You need to close the pointer object and connection object respectively. They have the same name.
Cursor. close ()
Conn. close ()

5 encoding (prevent garbled characters)

Notes:

1. Set the encoding for the Python file to UTF-8 (add # encoding = UTF-8 before the file)
2 MySQL database charset = UTF-8
3 Python is connected to MySQL with the charset = utf8 Parameter
4. Set the default Python encoding to UTF-8 (sys. setdefaultencoding (UTF-8)

# Encoding = UTF-8

Import sys

Import MySQLdb

Reload (sys)

Sys. setdefaultencoding ('utf-8 ')

Db = MySQLdb. connect (user = 'root', charset = 'utf8 ')
 

Note: MySQL Configuration File Settings must also be configured as utf8

 

6. module function demonstration

[Root @ localhost python] # more mysql. py
#! /Usr/bin/python

Import MySQLdb ### import module
Conn = MySQLdb. connect (user = "root", passwd = "ESBecs00! @ # ", Db =" test ") ### connect to mysql
Cursor = conn. cursor () ### obtain the link cursor

SQL = "select * from test" ### SQL statements
N=cursor.exe cute (SQL) ### Execute SQL
Print n ### if the execution succeeds, n is returned (n indicates the number of affected items, which is 3)

Row = cursor. fetchall () ### fetchall () returns all results, and fetchone () returns one result.
Print row ### print the returned results

Print "###################################### ################### dividing line #################### ##############################"

Cursor.exe cute (''' ### you can also directly place the SQL statement in it for execution.
Create table if not exists food (
Id int (10) primary key,
Name varchar (20 ),
Age int (10)
)
''')
SQL = "desc food"
Cursor.exe cute (SQL)
Row = cursor. fetchall ()
Print row

 

Cursor. close () ### close the cursor
Conn. close () ### close the connection

 

Execution result

[Root @ localhost python] # python mysql. py
3
('Hangsan',), ('lisi',), ('hangzhou ',))
######################################## #################
Mysql. py: 21: Warning: Table 'food' already exists
''')
('Id', 'int (10) ', 'No', 'pri', None, ''), ('name', 'varchar (20 )', 'Yes', '', None,''), ('age', 'int (10) ', 'yes', '', None ,'')
 

 

Run the following SQL statement to obtain the returned value:
// Obtain the connection cursor
Cursor = conn. cursor ()
// Query
SQL = "select * from [table ]"
Cursor.exe cute (SQL)

Return Value
Cur.exe cute ('select * from tables ')
The returned value is the number of rows obtained by the SQL statement. For example, 2L indicates 2 rows.
Then, you can obtain the row information from the fetchone or fetchall methods of the object.

Get row Information
The fetchone () method of the pointer object is to get the tuple return value of a row each time:
Reference
>>> Row = cur. fetchone ()
>>> Print row
('User1', '52c69e3a57331081823331c4e69d3f2e ', 1000L, 1000L,'/home/FTP/user1 ','')

The fetchall () method of the pointer object to retrieve all rows in the pointer result set. The returned result set is a tuples ):
Reference
>>> Cur. scroll (0, 'absolute ')
>>> Row = cur. fetchall ()
>>> Print row
('User1', '52c69e3a57331081823331c4e69d3f2e ', 1000L, 1000L,'/home/FTP/user1', ''), ('user2', 'hangzhou', 1000L, 1000L, 1000L, '/home/FTP/user2', None ))

Move pointer
When the fetchone () method is used, the pointer is moved. Therefore, if the pointer is not reset, The fetchall information will only contain the row content after the pointer.
Manual pointer movement:
Cur. scroll (int, parm)
Meaning:
Reference
Int: number of rows to be moved, an integer. In relative mode, positive values move downward, and negative values move upward.
Parm: The moving mode. The default mode is relative. The relative mode is acceptable. The absolute mode is absoulte.

Modify data
Modify data, including insert, update, and delete. They are all executed using the execute () method of the pointer object:
Cur.exe cute ("insert into table (row1, row2) values ('20140901', '20160901 ')")
Cur.exe cute ("update table set row1 = 'test' where row2 = 'row2 '")
Cur.exe cute ("delete from table where row1 = 'row1 '")

Because single quotation marks (') are used to identify SQL statements, strings in python must be enclosed by double quotation marks.
In addition, you can also use the "Format String" method in python to simplify the command, for example:
Cur.exe cute ("update table set row1 = '% s' where row2 =' % S'" % ('value1 ', 'value2 '))

※Note that the single quotation marks '% s' are SQL statement delimiters. The single quotation marks of 'value1' Are python string delimiters with different meanings. Whether a Delimiter is required, and whether double quotation marks or single quotation marks are used as the interval must be determined based on the meaning. For example, there are:
Cur.exe cute ("update FTPUSERS set passwd = % s where userid = '% S'" % ("md5 ('20140901')", 'user2 '))
Here, paswd = % s is because SQL's md5 () function does not require single quotation marks to be separated; "md5 ('000000')" is a python string containing single quotation marks, so it is enclosed in double quotation marks.

Submit changes
Generally, the MySQLdb module automatically submits modifications. After updating the data, run the following command manually:
Conn. commit ()

Close database connection
You need to close the pointer object and connection object respectively. They have the same name.
Cursor. close ()
Conn. close ()

You may also like the following article about Python:

Installation of Python in Linux and precautions

Install and use the Python rq module in Ubuntu 14.04

Directly run Python code without the need for the operating system

Install Python3.4 on CentOS source code

Python core programming version 2. (Wesley J. Chun). [Chinese version of hd pdf]

Python development technology details. (Zhou Wei, Zong Jie). [hd PDF scan version + book guide video + code]

Obtain Linux information using a Python script

Build a desktop algorithm transaction research environment using Python in Ubuntu

A Brief History of Python Development

Python details: click here
Python: click here

This article permanently updates the link address: