Python Learning notes-working with MySQL

There are two ways to operate MySQL in Python:

1. Pymysql Module

2. Sqlachemey of ORM Framework
   

This section first learns the first way.

Before learning the Python module, first look at MySQL basic installation and use, the specific syntax can refer to the bean before the blog http://beanxyz.blog.51cto.com/5570417/1609972

or official profile

https://mariadb.com/kb/en/mariadb/basic-sql-statements/

Simply review the setup of the basic environment:

Install MARIADB First (my environment is CentOS7)

Yum Install mariadb*systemctl start mariadb

Configuring firewalls

Firewall-cmd--add-port=3306/tcp--permanentsystemctl Restart Firewalld

Configure root Password

mysqladmin-u root password ' mysql ' mysql-uroot-p

Create a test database and table

MariaDB [(None)]> CREATE DATABASE mydb;  Query OK, 1 row Affected (0.00 sec) MariaDB [(none)]> use mydbdatabase changedmariadb [mydb]> CREATE TABLE student (ID int NOT null Auto_increment,name varchar (TEN), primary key (ID)); Query OK, 0 rows affected (0.04 sec) MariaDB [mydb]> insert into student (name) VALUES (' Jay '), (' Bob '), (' Alex '); Query OK, 3 Rows Affected (0.00 sec) records:3 duplicates:0 warnings:0mariadb [mydb]> select * FROM student;+----+- -----+| ID |  Name |+----+------+| 1 |  Jay | | 2 |  Bob | | 3 | Alex |+----+------+3 rows in Set (0.00 sec)

Create a remote access account

MariaDB [(None)]> create user [email protected]; Query OK, 0 rows Affected (0.00 sec) MariaDB [(none)]> set password for [email protected] ' 10.2.100.60 ' =password (' Yli '); Q Uery OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant all privileges on mydb.* to [email protected]; Query OK, 0 rows Affected (0.00 sec)

Then install a graphical interface tool Navicat, bind the database

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/89/F0/wKioL1gij7nxF_w0AADPQRGw3qE012.png "style=" float: none; "title=" 1.PNG "alt=" Wkiol1gij7nxf_w0aadpqrgw3qe012.png "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/89/F3/wKiom1gij7rh9UoaAADlgxB33VI628.png "style=" float: none; "title=" 2.PNG "alt=" Wkiom1gij7rh9uoaaadlgxb33vi628.png "/>

Such a basic test environment is built.

Now let's look at the use of pymysql.

Install the Pymysql module on my client

C:\windows\system32>pip Install pymysqlcollecting pymysql downloading PYMYSQL-0.7.9-PY3-NONE-ANY.WHL (78kB) 100% |# ###############################| 81kB 610kb/sinstalling collected packages:pymysqlsuccessfully installed pymysql-0.7.9

Python Source Demo

Inquire

#!/usr/bin/env python# -*- coding:utf-8 -*-import  pymysql# Open Database Connection conn = pymysql.connect (host= ' Sydnagios ',  port=3306, user= ' Yli ',  Passwd= ' Yli ',  db= ' mydb ') #创建一个游标对象cursor  = conn.cursor (cursor=pymysql.cursors.dictcursor) # SQL query Cursor.execute ("select * from student") #  gets the first row of data # row_1 =  Cursor.fetchone () # print (row_1) #  get the first n rows of data # row_2 = cursor.fetchmany (3) #  get all data row _3 = cursor.fetchall () print (row_3) #scroll可以使用相对位置或者绝对位置, where the relative position (end) moves up 2 lines cursor.scroll ( -2,mode= ' Relative ') Row_3 = cursor.fetchall () print (row_3) #提交, you cannot save the new data Conn.commit () #关闭游标cursor. Close () # Close Connection Conn.close ()-----------[{' id ': 1,  ' name ':  ' Jay '}, {' id ': 2,  ' name ':  ' Bob '}, {' id ': 3,  ' name ':  ' Alex '}][{' id ': 2,  ' name ':  ' Bob '}, {' id ':  3,  ' name ':  ' Alex '}] 

Modify

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' Sydnagios ', port=3306, user= ' Yli ' , passwd= ' Yli ', db= ' mydb ') cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.execute ("Update student set Name= ' BoB ' where id=2 ") cursor.execute (" SELECT * from student ") Row_3 = Cursor.fetchall () print (Row_3) conn.commit () Cursor.close () conn.close ()----------[{' id ': 1, ' name ': ' Chris '}, {' id ': 2, ' name ': ' BoB '}, {' id ': 3, ' name ': ' Alex '}]

Delete

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' Sydnagios ', port=3306, user= ' Yli ' , passwd= ' Yli ', db= ' mydb ') cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.execute ("Delete from student where id=2 ") cursor.execute (" SELECT * from student ") Row_3 = Cursor.fetchall () print (Row_3) conn.commit () Cursor.close () Conn.close ()----------[{' id ': 1, ' name ': ' Chris '}, {' id ': 2, ' name ': ' BoB '}, {' id ': 3, ' name ': ' Alex '}]

Add to

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysqlconn = pymysql.connect (host= ' Sydnagios ', port=3306, user= ' Yli ' , passwd= ' Yli ', db= ' mydb ') cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) cursor.execute ("INSERT INTO student (name) value (' Zhangsan '), (' LiSi ') ") cursor.execute (" SELECT * from student ") Row_3 = Cursor.fetchall () print (row_3) Conn.commit () Cursor.close () conn.close ()----------[{' Name ': ' Chris ', ' ID ': 1}, {' name ': ' Alex ', ' ID ': 3}, {' Name ': ' Zhangsan ', ' ID ': 4}, {' name ': ' LiSi ', ' ID ': 5}]

This article is from the "Mapo Tofu" blog, please be sure to keep this source http://beanxyz.blog.51cto.com/5570417/1871039

Python Learning notes-working with MySQL