Introduction to the basic knowledge of MySQL database programming in Python learning

In Python crawlers, it is usually stored in txt plain text, which can be stored in the database, and in the Wamp (Windows, Apache, MySQL, PHP, or Python) development Web site, but also through Python to build Web pages, So this article is mainly about Python calling MySQL database related programming knowledge

In Python crawlers, it is usually stored in txt plain text, which can be stored in the database, and in the Wamp (Windows, Apache, MySQL, PHP, or Python) development Web site, but also through Python to build Web pages, So this article is mainly about Python calling MySQL database related programming knowledge. This is explained in the following ways:

1. Configure MYSLQ
2.SQL Statement Basics
3.Python working with MySQL Basics
4.Python calling MySQL sample

I. Configuring MySQL

First download the mysql-5.0.96-winx64, as shown in the installation process.
1. Installing MySQL 5.0




2. Select manual configuration, service type, universal multifunction, and installation path




3. Set the number of database access connections to 15, Port 3306 (set URL in code), encoded as Utf-8




4. Set the default super root user name and password, and finally install successfully

Two. Basics of SQL statements

After you have successfully installed MySQL 5.0, perform a simple database operation.
1. Run MySQL input default user password 123456

2. Create a database test01 and use a database (the second call is directly used)
Create Database test01;

To display the database contained in the database: show databases;

3. Create table student, where the number is the primary key
CREATE TABLE student (username varchar, password varchar), Stuid int primary key);

4. Display the table structure, using the statement desc student

5. Insert the data into the student table and display the queried data

6. Delete tables: drop table student;

7. Updating data
Update student set password= ' 000000 ' where stuid= ' 1 ';

8. Delete Data
Delete from student where Username= ' eastmount;

At this point, the MySQL operation Database Basic explanation of the end, you can also implement database additions and deletions, transactions, stored procedures and other operations, it is recommended to install visual software to replace the black box, or use navicat for MySQL software. The code is as follows:

Enter password: ******
mysql> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| mysql       |
| performance_schema |
| test        |
| test01       |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test01;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(username varchar(20),
  ->         password varchar(20),
  ->         stuid int primary key);
Query OK, 0 rows affected (0.33 sec)

mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| student     |
+------------------+
1 row in set (0.00 sec)

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES |   | NULL  |    |
| password | varchar(20) | YES |   | NULL  |    |
| stuid  | int(11)   | NO  | PRI | NULL  |    |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> insert student(username, password, stuid)
  -> values('eastmount','123456',1)
  -> ;
Query OK, 1 row affected (0.05 sec)

mysql> select * from student;
+-----------+----------+-------+
| username | password | stuid |
+-----------+----------+-------+
| eastmount | 123456  |   1 |
+-----------+----------+-------+
1 row in set (0.00 sec)

mysql> update student set password='000000' where stuid='1';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;
+-----------+----------+-------+
| username | password | stuid |
+-----------+----------+-------+
| eastmount | 000000  |   1 |
+-----------+----------+-------+
1 row in set (0.00 sec)

mysql> delete from student where username='eastmount';
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql>

Three. Python calls MySQL basics

The usual installation method is to use: Pip install MySQL to install the Python MySQL library, but always error. Common errors such as:
Microsoft Visual C + + 9.0 is required (unable to find Vcvarsall.bat)
MYSQL.C: Fatal error C1083:cannot open include file: ' Config-win.h ': No such file or directory
These may be issues such as drivers.

Correct installation Method:
It is recommended to download a Mysql-python-1.2.3.win-amd64-py2.7.exe file for installation.
Website address: https://pypi.python.org/pypi/MySQL-python/

Below we will learn more about the Python database API. Accessing a database from Python requires an interface program, which is a Python module that provides an interface to the database client library (usually written in C) for you to access. Note: Python interface programs must adhere to the Python DB-API specification.
DB-API is a specification. It defines a set of required objects and database access methods to provide a consistent access interface for a wide variety of underlying database systems and a wide variety of database interface programs. DB-API provides a consistent access interface for different databases, and porting code between different databases becomes a breeze.

The following is a brief introduction to how DB-API is used.

1. Module Properties
The following attributes and attributes in the DB-API specification must be provided. A DB-API compatible module is defined as follows:

apilevel: module-compatible DB-API version number
threadsafety: thread safety level
paramstyle: support sql statement parameter style
connect (): connect to the database

Python calls MSSQL to import the MYSQLDB library, as follows:

import MySQLdb

2.connect () function

The main method used is the Connect object. The Connect () method generates a Connect object for accessing the database with the following parameters:

user:Username
password:Password
host:Hostname
database:DatabaseName
dsn:Data source name

Note Not all interface programs are strictly in this format, such as MySQLdb.

import MySQLdb
conn = MySQLdb.connect(host='localhost', db='test01', user='root', passwd='123456', port=3306, charset='utf8')

The Connect () object method is as follows:

close (): Close the database connection, or close the cursor object
commit (): Commit the current transaction
rollback (): cancel the current transaction
cursor (): Create a cursor or cursor-like object
errorhandler (cxn, errcls, errval): as a handle to a given cursor

Note that the close () method is executed and the Connection object method above is no longer available, or an exception occurs. Commit (), rollback (), cursor () may be more meaningful for a database that supports transactions.
A database transaction is a series of operations performed as a single logical unit of work, either completely or Transaction. Once you have completed the database connection, close the cursor object, then commit your operation in execution commit (), and then close the connection.

3. Cursor objects
It says that the connect () method is used to provide an interface to the database, and if you want to manipulate the database, you need to use a cursor object. Properties and methods for cursor objects:

fetchone (): can be seen as fetch (fetch) one (one), which is to get the next row (row) of the result set.
fetchmany (size): can be regarded as fetch (retrieve) many (multiple), the parameter here is the limit, get the next few rows (several rows) of the result set
fetchall (): As the name implies, get all.
execute (sql): execute a database operation, the parameter is a sql statement.
close (): Close as much as possible when the cursor is not needed

This is explained in a simple example.

Four. Python call MySQL sample

In the previous database we created the database "test01" and the Table "student", while inserting the data. So, how do you show it through Python?
1. Querying all databases
First, we look at the names of the databases contained in the local database through the "Show databases" statement.

import MySQLdb
 
try:
  conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306)
  cur=conn.cursor()
  res = cur.execute('show databases')
  print res
  for data in cur.fetchall():
    print '%s' % data
  cur.close()
  conn.close()
except MySQLdb.Error,e:
   print "Mysql Error %d: %s" % (e.args[0], e.args[1])

Where the database code is linked by:
Conn=mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' 123456 ', port=3306)
Access Root Superuser, whose password is "123456" and the port is "3306", the result is as follows:

If you do not know the name of the local database, you can use this method to query which databases are contained in the database before connecting to the database for related operations.

2. Enquiry Form
The following describes the query table student data, the code is as follows, the specific meaning of the code is to connect the database through connect (), define the cursor through the conn.cursor (), and then invoke the cursor Excute (SQL) to perform database operations, here is the query operation, All data is then obtained through the Fetchall () function.

# coding: utf-8
import MySQLdb
 
try:
   conn = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '123456', port = 3306, db = 'test01', charset = 'utf8')
   cur = conn.cursor ()
   res = cur.execute ('select * from student')
   print u 'table contains', res, u 'data \ n'
   print u 'data is as follows: (name password serial number)'
   for data in cur.fetchall ():
     print '% s% s% s'% data
   cur.close ()
   conn.close ()
except MySQLdb.Error, e:
    print "Mysql Error% d:% s"% (e.args [0], e.args [1])

Output Result:

The result of the corresponding MySQL is consistent and is the corresponding result.

3. Create a table
The following code is the creation of a teacher's table, mainly through commit () to submit data.

# coding:utf-8 
import MySQLdb
 
try:
  conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306, db='test01', charset='utf8')
  cur=conn.cursor()
  
  #查看表
  print u'插入前包含表:'
  cur.execute('show tables')
  for data in cur.fetchall():
    print '%s' % data

  #插入数据
  sql = '''create table teacher(id int not null primary key auto_increment,
                name char(30) not null,
                sex char(20) not null
     )'''
  cur.execute(sql)

  #查看表
  print u'\n插入后包含表:'
  cur.execute('show tables')
  for data in cur.fetchall():
    print '%s' % data
  cur.close()
  conn.commit()
  conn.close()
except MySQLdb.Error,e:
   print "Mysql Error %d: %s" % (e.args[0], e.args[1])

The output results are as follows, inserted into the teacher's table, containing fields: Teacher serial number (ID), teacher name, teacher gender (sex).

Inserting data can also be implemented through the Execute (SQL) method, such as:
Cur.execute ("INSERT into student values (' Yxz ', ' 111111 ', ' 10 ')")
But the new data that is inserted is usually assigned by a variable rather than fixed, so modify the value in the statement. We can make the following changes:

# coding: utf-8
import MySQLdb
 
try:
   conn = MySQLdb.connect (host = 'localhost', user = 'root', passwd = '123456', port = 3306, db = 'test01')
   cur = conn.cursor ()
  
   #Insert data
   sql = '' 'insert into student values (% s,% s,% s)' ''
   cur.execute (sql, ('yxz', '111111', '10'))

   #View data
   print u '\ nInsert data:'
   cur.execute ('select * from student')
   for data in cur.fetchall ():
     print '% s% s% s'% data
   cur.close ()
   conn.commit ()
   conn.close ()
except MySQLdb.Error, e:
    print "Mysql Error% d:% s"% (e.args [0], e.args [1])

The output results are as follows:

>>>
Insert data:
esatmount 123456 1
yangxiuzhang 123456 2
xiaoy 123456 3
yxz 111 111 10
>>> 

In the same way, the database can be changed or deleted, please read the reader by themselves.
Recommended data: Python using MySQL database-Bug Master
I'll follow the Python crawler to tell you how to store crawled content in a database, such as my csdn blog, crawling blog titles, publishing times, reading volumes, and comments.

The results in the MySQL database are as follows:

Finally hope that the article is helpful to you, if there is insufficient or wrong in the article, also please Haihan ~ or that sentence, very enjoy the current teacher life, regardless of scientific research, projects, or teaching, very substantial, refueling!