Use MySQL Databases in Python

Use MySQL Databases in Python

1. Install mysql
Sudo apt-get install mysql-server
Sudo apt-get install mysql-client

2. Install the MySQL-python driver
Sudo apt-get install mysql-python

3. test whether the installation is successful.
Run import MySQLdb In the python interpreter command.
If no error is reported, the installation is successful.

4. python using mysql database Common Operations exmysql. py

#-*-Coding: UTF-8 -*-
Import MySQLdb

Try:
# Mysql connection
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = 'Password', port = 3306, charset = 'utf8 ')

# Create a cursor
Cur = conn. cursor ()

# Creating a database
Cur.exe cute ('create database if not exists pythondb ')

# Switch to database
Conn. select_db ('pythondb ')

# Creating a table
Cur.exe cute ("create table student (id int, name varchar (20), age int )")

# Insert a single data entry
Cur.exe cute ("insert into student values (9, 'zhang san', 28 )")

# Insert multiple data entries
Cur.exe cute ("insert into student values (2, 'shijingjing08', 21), (3, 'shijingjing09', 30), (4, 'shijingjing10', 12 )")

# Update data
Cur.exe cute ("update student set age = 27 where name = 'shijingjing07 '")

# Deleting data
Cur.exe cute ("delete from student where name = 'shijingjing10 '")

# Search for Data
Count = cur.exe cute ("select * from student ")
Print "Total"
Print count

Result = cur. fetchone ()
Print "find one"
Print result

Results = cur. fetchtasks (5)
Print "Search for multiple items"
For r in results:
Print r

Cur. scroll (0, mode = 'absolute ')

# Cursor to the first data in the table
Results = cur. fetchall ()

Print "Search for all data, values in the second column"
For r in results:
Print r [1]

# When executing the select query again to return the result set, you need to execute the nextset () operation and move it to the next result set. Otherwise, error 2014 will be reported.
Cur. nextset ()

# Two Methods for executing a stored procedure
Export cur.exe cute ("call get_student ('shijingjing', 20 )")
Cur. callproc ("get_student", ('shijingjing', 20 ))
Print "execution stored procedure"
Data = cur. fetchall ()
For d in data:
Print d

# Closing a cursor
Cur. close ()

# Submit
Conn. commit ()
# Closing database connection
Conn. close ()

Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

 

Stored Procedure get_student (in p_name varchar (20), in p_age int), input name, age parameter, fuzzy search name, and age> p_age student.

Delimiter $
Create procedure get_student (in p_name varchar (20), in p_age int)
Begin
Select * from student where name like concat (p_name, '%') and age> p_age;
End;
$

5. Running result:

6. Common Operations:
Commit () Submit
Rollback () rollback

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

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.