Introduction:
Python standard database interface is Python DB-API. Python DB-API provides database application programming interface for developers.
Can access Python database interface and API View a detailed list of supported databases.
For different databases, you need to download different DB API modules. For example, you need to access Oracle database and Mysql data. You need to download Oracle and MySQL database modules.
DB-API is a specification. It defines a series of necessary objects and data inventory access methods in order to provide a consistent access interface for a variety of underlying database systems and a variety of database interface programs.
Python's DB-API implements interfaces for most databases. Once it connects to each database, it can operate each database in the same way.
Python DB-API usage process:
- Introduce API module.
- Get the connection to the database.
- Execute SQL statements and stored procedures.
- Close the database connection.
First, install mysql
If you are a windows user, the installation of MySQL is very simple. You can download the installation file directly and double-click the installation file step by step. Installation under Linux, in addition to downloading installation packages for installation, the general Linux warehouse will have mysql, we only need a command to download and install:
Ubuntu\deepin
>>sudo apt-get install mysql-server
>>Sudo apt-get install mysql-client
centOS/redhat
>>yum install mysql
Visit http://sourceforge.net/projects/mysql-python
(Linux platform accessible: https://pypi.python.org/pypi/MySQL-python)
From here, you can choose installation packages, which are divided into pre-compiled binary files and source code installation packages.
If the binary distribution version is selected, the installation process can be completed with basic installation tips. If you install from source code, you need to switch to the top-level directory of the MySQLdb distribution and type the following commands:
$ gunzip MySQL-python-1.2.2.tar.gz $ tar -xvf MySQL-python-1.2.2.tar $ cd MySQL-python-1.2.2 $ python setup.py build $ python setup.py install
Second, install MySQL-python
To enable Python to operate mysql, it needs MySQL-python driver, which is an essential module for Python to operate mysql.
Download address: https://pypi.python.org/pypi/MySQL-python/
After downloading the MySQL-python-1.2.5.zip file, decompress it directly. Enter MySQL-python-1.2.5 directory:
>>python setup.py install
Three, test
The test is very simple to check whether the MySQLdb module can be imported properly.
>>> import MySQLdb
Fourth, the basic operation of mysql
Mysql-u root-p (when password) Mysql-u root (without a password)
mysql> show databases; #View all current databases +--------------------+ | Database | +--------------------+ | information_schema | | csvt | | csvt04 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.18 sec) mysql> use test; #Using test database Database changed mysql> show tables; #Look at the table below the test library Empty set (0.00 sec) #Create user table, name and password fields mysql> CREATE TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected (0.27 sec) #Insert several pieces of data into the user table mysql> insert into user values('Tom','1321'); Query OK, 1 row affected (0.05 sec) mysql> insert into user values('Alen','7875'); Query OK, 1 row affected (0.08 sec) mysql> insert into user values('Jack','7455'); Query OK, 1 row affected (0.04 sec) #View the data in the user table mysql> select * from user; +------+----------+ | name | password | +------+----------+ | Tom | 1321 | | Alen | 7875 | | Jack | 7455 | +------+----------+ 3 rows in set (0.01 sec) #Delete data whose name equals Jack mysql> delete from user where name = 'Jack'; Query OK, 1 rows affected (0.06 sec) #Modify the password of name equal to Alen to 1111 mysql> update user set password='1111' where name = 'Alen'; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 #View table contents mysql> select * from user; +--------+----------+ | name | password | +--------+----------+ | Tom | 1321 | | Alen | 1111 | +--------+----------+ 3 rows in set (0.00 sec)
Fifth, python operates mysql database foundation
#coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='localhost', port = 3306, user='root', passwd='123456', db ='test', ) cur = conn.cursor() #Create data tables cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))") #Insert a piece of data cur.execute("insert into student values('2','Tom','3 year 2 class','9')") #Data to modify query conditions cur.execute("update student set class='3 year 1 class' where name = 'Tom'") #Delete data for query conditions cur.execute("delete from student where age='9'") cur.close() #Close cursor conn.commit() #Submit sql statements conn.close() #Close connection
>>> conn=MySQLdb.connect(host='localhost',port=3306,user='root',passwd='123456',db ='test',)
The Connect() method is used to create a connection to a database, in which parameters can be specified: user name, password, host information, etc.
It's just connected to the database, and you need to create cursors to manipulate the database.
>>> cur = conn.cursor()
cursor() method under conn is used to create cursor.
>>> cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))")
execute() method can be written to pure sql statements by cur sor operation. Data is manipulated by writing sql statements in the execute() method.
>>>cur.close()
cur.close() closes the cursor
>>>conn.commit()
The conn.commit() method is used to submit things. It must be used when inserting a data into the database, otherwise the data will not be really inserted.
>>>conn.close()
Conn.close() Closes database connections
Sixth, insert data
It is inconvenient to insert data by writing pure sql statements in the execute() method above. Such as:
>>>cur.execute("insert into student values('2','Tom','3 year 2 class','9')")
If I want to insert new data, I have to modify the value in this statement. We can make the following modifications:
#coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='localhost', port = 3306, user='root', passwd='123456', db ='test', ) cur = conn.cursor() #Insert a piece of data sqli="insert into student values(%s,%s,%s,%s)" cur.execute(sqli,('3','Huhu','2 year 1 class','7')) #Insertion using formatting cur.close() conn.commit() conn.close()
Insert multiple values into a data table at a time
#coding=utf-8 import MySQLdb conn= MySQLdb.connect( host='localhost', port = 3306, user='root', passwd='123456', db ='test', ) cur = conn.cursor() #Insert multiple records at a time sqli="insert into student values(%s,%s,%s,%s)" cur.executemany(sqli,[ ('3','Tom','1 year 1 class','6'), ('3','Jack','2 year 1 class','7'), ('3','Yaheng','2 year 2 class','7'), ]) cur.close() conn.commit() conn.close()
Database query operation
Python queries Mysql to get single data using fetchone() method, and fetchall() method to get multiple data.
fetchone(): This method obtains the next query result set. The result set is an object
fetchall(): Receives all returned result rows.
rowcount: This is a read-only property and returns the number of rows affected by the execution of the execute() method.
Example:
Query all data in EMPLOYEE table with salary field greater than 1000:
#!/usr/bin/python # -*- coding: UTF-8 -*- import MySQLdb # Open database connection db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # Using cursor() method to get operation cursor cursor = db.cursor() # SQL Query Statement sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > '%d'" % (1000) try: # Executing SQL statements cursor.execute(sql) # Get a list of all records results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Print results print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \ (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # Close database connection db.close()
Other statements remain unchanged:
Database update operation
The update operation is used to update the data of the data table. The following example increments the SEX field of the EMPLOYEE table to the AGE field of'M', by 1:
# SQL UPDATE statement sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
Delete operation
Delete operation is used to delete data in data table. The following example demonstrates all data whose AGE is greater than 20 in EMPLOYEE:
# SQL Delete Statement sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
Executive affairs
Transaction mechanism can ensure data consistency.
Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are commonly referred to as ACID features.
atomicity: A transaction is an inseparable unit of work. The operations involved in a transaction are either done or not done.
consistency: A transaction must change a database from one consistent state to another. consistency is closely related to atomicity.
isolation: The execution of a transaction cannot be interfered with by other transactions. That is to say, the operation and data used within a transaction are isolated from other concurrent transactions, and the transactions executed concurrently can not interfere with each other.
durability: Persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other subsequent operations or failures should not have any impact on it.
Transactions in Python DB API 2.0 provide two methods, commit or rollback.
# SQL Delete Record Statement sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) try: # Executing SQL statements cursor.execute(sql) # Submit to database db.commit() except: # Roll back when an error occurs db.rollback()