Installation and connection of python3.6 with MySQL

Because of the similarity of text, we need a lot of data, but also need to store the data, the ID of the text can be extracted directly when the comparison is made.

First to install MySQL, I was downloaded from the Baidu Software library MySQL,

Installation

Then install, I downloaded from the official website, can not be used, may have been installed before the MySQL, Baidu Software This is installed with 32/64 bit, you can choose,

Although the name is 5.6.24, but the installation of MySQL is 5.7.17 version of the need to note that before the installation you need to download and install Python4.3, Because the software installed only with python3.4 connector, so need to implement installation, my installation process is mainly read a book called "Python 3 Basic Tutorial" there happens to have this installation step

Installation needs attention:

1. Before installing you need to download and install the Python4.3, because the software installed only with python3.4 connector

2.port:3306

After the 3.MYSQL installation is complete, add the Bin folder under the installation directory to the system environment variable path so that the Mysql-u Root-p is entered to start MySQL

4. There is no need to modify the My-default.ini.

How do I connect the python3.6 to MySQL?

1.PyMySQL is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2. So I need pip install Pymysql

2. To connect the database, we can start MySQL in cmd, or open

To start, first we need to create a new database under MySQL, such as:mysql> create DATABASE mydb; Then use MyDB in mysql>; CREATE TABLE students (creates a students table)

3. In Python compilation, the creation of the table to insert, modify, delete and other operations need to start MySQL, but also have mysql> use mydb; (That is, the database where the table resides)

Use an example to illustrate: The main reference is http://www.cnblogs.com/hank-chen/p/6624299.html, but also some of the bugs inside it has been changed.

[HTML]View PlainCopy

1. MySQL> CREATE database mydb;

The first time we started testing This example, we created a money table, but the Python compiler appears

So re-created a Save.money (mainly to the previous table to delete, otherwise there will be an error), I used the ' id ', there was a syntax error, so the direct ID on the line, do not have the quoted number.

[Python]View PlainCopy

2. Import Pymysql
6. # Connect to Database
8. Connect = Pymysql. Connect (
10. host=' localhost ',
12. port=3306,
14. user=' root ',
16. passwd=' 1234 ',
18. db=' save ',
20. charset=' UTF8 '
22. )
26. # get cursor
28. cursor = Connect.cursor ()
32. # Insert Data
34. sql = "INSERT into Money (name, account, saving) VALUES ('%s ', '%s ',%.2f)"
36. data = (' Lei June ', ' 13512345678 ', 10000)
38. Cursor.execute (sql% data)
40. Connect.commit ()
42. Print (' successful insert ', Cursor.rowcount, ' data ')
46. # Modify Data
48. sql = "UPDATE money SET saving =%.2f WHERE account = '%s '"
50. data = (8888, ' 13512345678 ')
52. Cursor.execute (sql% data)
54. Connect.commit ()
56. Print (' successfully modified ', Cursor.rowcount, ' data ')
60. # Querying data
62. sql = "Select name,saving from Money WHERE account = '%s '"
64. data = (' 13512345678 ',)
66. Cursor.execute (sql% data)
68. For row in Cursor.fetchall ():
70. print ("name:%s\tsaving:%.2f"% row)
72. Print (' co-search Find ', Cursor.rowcount, ' data ')
76. # Delete Data
78. sql = "DELETE from Money WHERE account = '%s ' LIMIT%d"
80. data = (' 13512345678 ', 1)
82. Cursor.execute (sql% data)
84. Connect.commit ()
86. Print (' successful delete ', Cursor.rowcount, ' data ')
90. # Transaction Processing
92. Sql_1 = "UPDATE money SET saving = saving + WHERE account = ' 18012345678 '"
94. sql_2 = "UPDATE money SET expend = expend + WHERE account = ' 18012345678 '"
96. Sql_3 = "UPDATE Money SET income = income + $ WHERE account = ' 18012345678 '"
100. Try
102. Cursor.execute (Sql_1) # Savings Increase
104. Cursor.execute (sql_2) # increased spending
106. Cursor.execute (Sql_3) # Revenue increase
108. Except Exception as E:
110. Connect.rollback () # transaction Rollback
112. print (' transaction failed ', e)
114. Else
116. Connect.commit () # Transaction Commit
118. print (' transaction success ', Cursor.rowcount)
122. # Close Connection
124. Cursor.close ()
126. Connect.close ()

The results are as follows:

Additional MySQL related knowledge:

How to build a database with MySQL

Start MySQL

View Existing Databases
mysql> show databases;

Create a database (if the database is named MyDB)
mysql> CREATE DATABASE MyDB;

Delete the database (if the database name is MyDB)
mysql> drop database accounts;

Working with databases (if using Database MyDB)
mysql> use MyDB;
Once you have finished using the database command, you can create, modify, insert, delete tables, and so on.

MySQL Database How to create a data table and add data

Use the CREATE TABLE statement to complete the creation of a table, the common form of CREATE TABLE:
CREATE TABLE table name (column declaration);
To create the students table, for example, the table will hold the number (ID), name, Gender (sex), Age, contact phone (tel) content:
CREATE TABLE Students
（
ID int unsigned NOT NULL Auto_increment primary key,
Name Char (8) is not NULL,
Sex char (4) NOT NULL,
Age tinyint unsigned is not NULL,
Tel char (+) NULL default "-"
);

Inserting data into a table
The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:
Insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...);
where [] The content is optional, for example, to insert a record into the students table in the SAMP_DB database, execute the statement:
INSERT into students values (NULL, "Wang Gang", "male", 20, "13811371377");
Press ENTER to confirm that if you prompt Query Ok, 1 row affected (0.05 sec) indicates that the data was inserted successfully. If the insertion fails, check that the database you want to manipulate is selected.

Sometimes we just need to insert some of the data, or not in the order of the columns, you can insert them in this form:
INSERT into students (name, sex, age) VALUES ("Sun Lihua", "female", 21);

Installation and connection of python3.6 with MySQL