Python Topic 9 basic knowledge of Mysql database programming, pythonmysql

Python Topic 9 basic knowledge of Mysql database programming, pythonmysql

In Python web crawlers, TXT files are usually stored in plain text, but they can also be stored in databases. At the same time, WAMP (Windows, Apache, MySQL, PHP, or Python) in the Development website, you can also build web pages through Python. Therefore, this article mainly describes the programming knowledge of Python calling MySQL database. We will explain the following aspects:

1. Configure MySLQ
2. Basic knowledge of SQL statements
3. Basic knowledge of using Python to operate MySQL
4. Example of Python calling MySQL

1. Configure MySQL

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

 

2. Select manual configuration, service type, Universal multi-function type, and installation path
 

 

3. Set the connection count of the database to 15, the port to 3306 (used in the URL setting in the Code), and the encoding method to UTF-8.
 

 

4. Set the username and password of the default Super root user, and the installation is successful.
 

Ii. Basic knowledge of SQL statements

After successful installation of MySQL 5.0, perform simple database operations.
1. Run MySQL and enter the Default User Password 123456

2. Create the database test01 and use the database (the second call directly uses the database)
Create database test01;

Display the databases in the database: show databases;

3. Create a table student with the student ID as the primary key.
Create table student (username varchar (20), password varchar (20), stuid int primary key );

4. display the table structure. Use the desc student statement.

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

6. delete a table: drop table student;

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

8. delete data
Delete from student where username = 'astmount;

At this point, the basic explanation of MySQL database operations is complete. You can also perform operations such as adding, deleting, modifying, and querying databases, transactions, and stored procedures. We recommend that you install visual software to replace the black box, or use Navicat for MySQL. 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 changedmysql> 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: 0mysql> 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>

III. Basic knowledge about calling MySQL using Python

The usual installation method is to use pip install mysql to install the Python MySQL library, but an error is always reported. Common Errors include:
Microsoft Visual C ++ 9.0 is required (Unable to find vcvarsall. bat)
Mysql. c (42): fatal error C1083: Cannot open include file: 'config-win. H': No such file or directory
These may be driver issues.

Correct installation method:
Download the mysql-python-1.2.3.win-amd64-py2.7.exe file for installation.
Address: https://pypi.python.org/pypi/MySQL-python/
: Http://www.bkjia.com/softs/73369.html

The following describes the Python database API in detail. Accessing a database from Python requires an interface program. The interface program is a Python module that provides an interface for you to access the database client library (usually written in C. Note: Python interface programs must comply with the Python DB-API specifications.
DB-API is a specification. It defines a series of required objects and database access methods to provide consistent access interfaces for a variety of underlying database systems and a variety of database interface programs. DB-API provides consistent access interfaces for different databases, and porting code between different databases makes it easy.

The following briefly introduces the use of DB-API.

1. Module attributes
The following characteristics 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 security level paramstyle: Support for SQL statement parameter style connect (): connect to the database

Python needs to import the MySQLdb library to call MsSQL, as follows:

import MySQLdb

 2. connect () function

The main method is the connect object. The connect () method generates a connect object for accessing the database. Its parameters are as follows:

user:Usernamepassword:Passwordhost:Hostnamedatabase:DatabaseNamedsn:Data source name

Note that not all interface programs strictly follow this format, such as MySQLdb.

import MySQLdbconn = 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 (): Submit the current transaction rollback (): cancel the current transaction cursor (): Create a cursor or class cursor object errorhandler (cxn, errcls, errval): Used as the handle to the cursor.

Note: If you execute the close () method, the above connection object method cannot be used any more; otherwise, an exception occurs. Commit (), rollback (), and cursor () may be more meaningful for databases that support transactions.
Database Transaction refers to a series of operations performed as a single logical unit of work, either completely or completely. Once you have completed the database connection, close the cursor object, submit your operation in the execution of commit (), and then close the connection.

3. cursor object
The connect () method is used to provide interfaces for connecting to the database. To operate the database, you also need to use a cursor object. Attributes and methods of the cursor object:

Fetchone (): it can be regarded as fetch (fetch) one (one), that is, the next row (one row) of the result set ). Fetchall (size): it can be considered as fetch (fetch) distinct (multiple). Here the parameter is a boundary. The following rows (several rows) of the result set fetchall () are obtained: as the name suggests, get all. Execute (SQL): execute database operations. The parameter is an SQL statement. Close (): close the cursor whenever possible.

The following is a simple example.

Iv. Example of Python calling MySQL

In the previous database, we created the Database "test01" and the table "student", and inserted data at the same time. So, how can we use Python to display it?
1. query all databases
First, we can view the name of the database contained in the local database and use 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])

The code for connecting to the database is:
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', port = 123456)
To access the root Super User, set the password to "123456" and the port to "3306". The result is as follows:

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

2. query the table
The following describes how to query the data in student. The Code is as follows. The specific meaning of the code is to connect to the database through connect () and connect to the database through conn. cursor () defines the cursor, and then calls the cursor's excute (SQL) to perform database operations. Here it is the query operation, and then obtains all data through the fetchall () function.

# Coding: UTF-8 import MySQLdb try: conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', port = 123456, db = 'test01', charset = 'utf8') cur = conn. cursor () res = cur.exe cute ('select * from student ') print U' table contains', res, u'data \ n' print U' data is as follows: (name and password number) 'for data in cur. fetchall (): print '% s % s' % data cur. close () conn. close () distinct T MySQLdb. error, e: print "Mysql Error % d: % s" % (e. args [0], e. args [1])

Output result:

The results in the corresponding MySQL are consistent and are the corresponding results.

 3. Create a table
The following code creates a instructor table and submits data through commit.

# Coding: UTF-8 import MySQLdb try: conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', port = 123456, db = 'test01', charset = 'utf8') cur = conn. cursor () # view the print U' table that contains the table before insertion: 'cur.exe cute ('show tables ') for data in cur. fetchall (): print '% s' % data # insert data SQL = ''' create table teacher (id int not null primary key auto_increment, name char (30) not null, sex char (20) not null) ''' cur.exe cute (SQL) # view the table print U' \ n after insertion, including the table 'cur.exe cute ('show tables ') for data in cur. fetchall (): print '% s' % data cur. close () conn. commit () conn. close () distinct T MySQLdb. error, e: print "Mysql Error % d: % s" % (e. args [0], e. args [1])

The output result is as follows: insert the instructor table, which contains the following fields: Instructor id, instructor name, and sex ).

You can also use the execute (SQL) method to insert data, for example:
Cur.exe cute ("insert into student values ('yz', '123', '10 ')")
However, the new data inserted is usually assigned values through variables instead of fixed values. Therefore, you must modify the values in this statement. We can make the following changes:

# Coding: UTF-8 import MySQLdb try: conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', port = 123456, db = 'test01') cur = conn. cursor () # insert data SQL = ''' insert into student values (% s, % s, % s) ''' cur.exe cute (SQL, ('yz ', '200', '10') # View data print U' \ n insert data: 'cur.exe cute ('select * from student ') for data in cur. fetchall (): print '% s % s' % data cur. close () conn. commit () conn. close () distinct T MySQLdb. error, e: print "Mysql Error % d: % s" % (e. args [0], e. args [1])

The output result is as follows:

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

Similarly, you can add, delete, modify, and insert databases. Please read them by yourself.
Recommended materials: use mysql database in python-wormhole
Later, I will talk about how to store crawled content in a database, such as my CSDN blog, and how to crawl the blog title, release time, reading volume, and comment count.

The results in the MySQL database are shown in:

Finally, I hope the article will help you. If there are deficiencies or errors in the article, please try again ~ In other words, I enjoyed my current teacher's life. No matter in scientific research, projects, or teaching, I am very full. Come on!