Common functions of python database operations (create tables, insert data, and obtain data)

Instance 1. Obtain the MYSQL version

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
# Install mysql db for python
Import MySQLdb as mdb
Con = None
Try:
# Method for connecting to mysql: connect ('IP', 'user', 'Password', 'dbname ')
Con = mdb. connect ('localhost', 'root ',
'Root', 'test ');
# All queries are run on the cursor module connected to con.
Cur = con. cursor ()
# Execute a query
Cur.exe cute ("select version ()")
# Obtain the result of the previous query, which is a single result
Data = cur. fetchone ()
Print "Database version: % s" % data
Finally:
If con:
# Remember to close the connection anyway
Con. close ()

Execution result:
Database version: 5.5.25

Instance 2. Create a table and insert data

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
Import MySQLdb as mdb
Import sys
# Set con as a global connection
Con = mdb. connect ('localhost', 'root', 'root', 'test ');
With con:
# Obtain the connected cursor. operations can be performed only when the obtained cursor is obtained.
Cur = con. cursor ()
# Create a data table writers (id, name)
Cur.exe cute ("create table if not exists \
Writers (Id int primary key AUTO_INCREMENT, Name VARCHAR (25 ))")
# Five data entries are inserted below
Cur.exe cute ("insert into Writers (Name) VALUES ('Jack London ')")
Cur.exe cute ("insert into Writers (Name) VALUES ('honore de balzac ')")
Cur.exe cute ("insert into Writers (Name) VALUES ('lion Feuchtwanger ')")
Cur.exe cute ("insert into Writers (Name) VALUES ('emile Zola ')")
Cur.exe cute ("insert into Writers (Name) VALUES ('Truman Capote ')")

Instance 3. python uses slect to obtain mysql Data and traverse it

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
Import MySQLdb as mdb
Import sys
# Connect to mysql to obtain the connected object
Con = mdb. connect ('localhost', 'root', 'root', 'test ');
With con:
# Still, the first step is to obtain the connected cursor object for query.
Cur = con. cursor ()
# Like query functions in other languages, execute is the execution query function in python.
Cur.exe cute ("SELECT * FROM Writers ")
# Use the fetchall function to store the result set (multi-dimensional tuples) into rows.
Rows = cur. fetchall ()
# Traverse the result set in sequence and find that each element is a record in the table, which is displayed with a single tuple
For row in rows:
Print row

Copy codeThe Code is as follows:
Execution result:
(1L, 'Jack London ')
(2L, 'honore de balzac ')
(3L, 'on' Feuchtwanger ')
(4L, 'emile Zola ')
(5L, 'Truman Capote ')

Instance 4. Use the dictionary cursor to obtain the result set (you can use the table field name to access the value)

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
# Source: crazy ant's blog www.server110.com
Import MySQLdb as mdb
Import sys
# Obtain the link object queried by mysql
Con = mdb. connect ('localhost', 'root', 'root', 'test ')
With con:
# Obtain the connected dictionary cursor. Pay attention to the obtained method,
# Each cursor is actually a subclass of cursor.
Cur = con. cursor (mdb. cursors. DictCursor)
# The execution statement remains unchanged.
Cur.exe cute ("SELECT * FROM Writers ")
# The method for obtaining data remains unchanged
Rows = cur. fetchall ()
# Data traversal remains unchanged (a little more direct than the previous one)
For row in rows:
# Here, you can use the key-Value Pair method to obtain data by the key name.
Print "% s" % (row ["Id"], row ["Name"])

Instance 5. How to obtain the field names and information of a single table

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
# Source: crazy ant's blog www.server110.com
Import MySQLdb as mdb
Import sys
# Retrieving Database Link objects
Con = mdb. connect ('localhost', 'root', 'root', 'test ')
With con:
# Retrieving normal query cursor
Cur = con. cursor ()
Cur.exe cute ("SELECT * FROM Writers ")
Rows = cur. fetchall ()
# Get the description of the connection object
Desc = cur. description
Print 'cur. description: ', desc
# Print the header, that is, the field name
Print "% s % 3 s" % (desc [0] [0], desc [1] [0])
For row in rows:
# Print the result
Print "% 2 s % 3 s" % row

Copy codeThe Code is as follows:
Running result: cur. description: ('id', 3, 1, 11, 11, 0, 0), ('name', 253, 17, 25, 25, 0, 1 ))
Id Name
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Instance 6: Use Prepared statements to perform queries (more secure and convenient)

Copy codeThe Code is as follows:
#-*-Coding: UTF-8 -*-
# Source: crazy ant's blog www.server110.com
Import MySQLdb as mdb
Import sys
Con = mdb. connect ('localhost', 'root', 'root', 'test ')
With con:
Cur = con. cursor ()
# We can see that you can write an SQL statement that can be assembled to execute
Cur.exe cute ("UPDATE Writers SET Name = % s WHERE Id = % s ",
("Guy de Maupasant", "4 "))
# Use cur. rowcount to obtain the number of affected rows
Print "Number of rows updated: % d" % cur. rowcount

Result:
Copy codeThe Code is as follows:
Number of rows updated: 1