Installation and connection of python3.6 with MySQL

Source: Internet
Author: User
Tags mysql in rowcount

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
  1. Import Pymysql
  2. # Connect to Database
  3. Connect = Pymysql. Connect (
  4. host=' localhost ',
  5. port=3306,
  6. user=' root ',
  7. passwd=' 1234 ',
  8. db=' save ',
  9. charset=' UTF8 '
  10. )
  11. # get cursor
  12. cursor = Connect.cursor ()
  13. # Insert Data
  14. sql = "INSERT into Money (name, account, saving) VALUES ('%s ', '%s ',%.2f)"
  15. data = (' Lei June ', ' 13512345678 ', 10000)
  16. Cursor.execute (sql% data)
  17. Connect.commit ()
  18. Print (' successful insert ', Cursor.rowcount, ' data ')
  19. # Modify Data
  20. sql = "UPDATE money SET saving =%.2f WHERE account = '%s '"
  21. data = (8888, ' 13512345678 ')
  22. Cursor.execute (sql% data)
  23. Connect.commit ()
  24. Print (' successfully modified ', Cursor.rowcount, ' data ')
  25. # Querying data
  26. sql = "Select name,saving from Money WHERE account = '%s '"
  27. data = (' 13512345678 ',)
  28. Cursor.execute (sql% data)
  29. For row in Cursor.fetchall ():
  30. print ("name:%s\tsaving:%.2f"% row)
  31. Print (' co-search Find ', Cursor.rowcount, ' data ')
  32. # Delete Data
  33. sql = "DELETE from Money WHERE account = '%s ' LIMIT%d"
  34. data = (' 13512345678 ', 1)
  35. Cursor.execute (sql% data)
  36. Connect.commit ()
  37. Print (' successful delete ', Cursor.rowcount, ' data ')
  38. # Transaction Processing
  39. Sql_1 = "UPDATE money SET saving = saving + WHERE account = ' 18012345678 '"
  40. sql_2 = "UPDATE money SET expend = expend + WHERE account = ' 18012345678 '"
  41. Sql_3 = "UPDATE Money SET income = income + $ WHERE account = ' 18012345678 '"
  42. Try
  43. Cursor.execute (Sql_1) # Savings Increase
  44. Cursor.execute (sql_2) # increased spending
  45. Cursor.execute (Sql_3) # Revenue increase
  46. Except Exception as E:
  47. Connect.rollback () # transaction Rollback
  48. print (' transaction failed ', e)
  49. Else
  50. Connect.commit () # Transaction Commit
  51. print (' transaction success ', Cursor.rowcount)
  52. # Close Connection
  53. Cursor.close ()
  54. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.