How to use python to operate database (mysql)-Python Tutorial-php.cn

Table of Contents

2. Connect python to the database" >2. Connect python to the database

3. Transaction rollback" >3. Transaction rollback

4. Insert data in batches" >4. Insert data in batches

Home

Backend Development

Python Tutorial

How to use python to operate database (mysql)

迷茫

Mar 25, 2017 pm 04:51 PM

1. Basic database operations

1. If you want to allow writing Chinese in the database, you can use the following command when creating the database

create database zcl charset utf8;

Copy after login

2. View the students table structure

desc students;

Copy after login

3. View the statements that create the students table structure

show create table students;

Copy after login

4. Delete the database

drop database zcl;

Copy after login

5. Create a new field

alter table students add column nal char(64);

Copy after login

PS: I hate the above " Simple explanation + code" blog. In fact, I wrote a lot of examples in the mysql terminal, but because the computer was running a video-watching software at the time, I couldn't Ctrl+C/V. I’m too lazy now haha~~

2. Connect python to the database

python3 no longer supports mysqldb. Its replacement module is PyMySQL. The example in this article is in the python3.4 environment.

1. Install the pymysql module

pip3 install pymysql

Copy after login

2. Connect to the database and insert the data instance

import pymysql
#生成实例，连接数据库zcl
conn = pymysql.connect(host=&#39;127.0.0.1&#39;, user=&#39;root&#39;, passwd=&#39;root&#39;, db=&#39;zcl&#39;)
#生成游标，当前实例所处状态
cur = conn.cursor()
#插入数据
reCount = cur.execute(&#39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&#39;,(&#39;Jack&#39;,&#39;man&#39;,25,1351234,"CN"))
reCount = cur.execute(&#39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&#39;,(&#39;Mary&#39;,&#39;female&#39;,18,1341234,"USA"))

conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount)

Copy after login

View results:

mysql> select* from students;+----+------+-----+-----+-------------+------+
| id | name | sex | age | tel         | nal  |
+----+------+-----+-----+-------------+------+
|  1 | zcl  | man |  22 | 15622341234 | NULL |
|  2 | alex | man |  30 | 15622341235 | NULL |
+----+------+-----+-----+-------------+------+
2 rows in set

Copy after login

3. Get data

import pymysql

conn = pymysql.connect(host=&#39;127.0.0.1&#39;, user=&#39;root&#39;, passwd=&#39;root&#39;, db=&#39;zcl&#39;)
cur = conn.cursor()

reCount = cur.execute(&#39;select* from students&#39;)

res = cur.fetchone()       #获取一条数据
res2 = cur.fetchmany(3)   #获取3条数据
res3 = cur.fetchall()     #获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()

cur.close()
conn.close()

Copy after login

Output:

(1, &#39;zcl&#39;, &#39;man&#39;, 22, &#39;15622341234&#39;, None)
((2, &#39;alex&#39;, &#39;man&#39;, 30, &#39;15622341235&#39;, None), (5, &#39;Jack&#39;, &#39;man&#39;, 25, &#39;1351234&#39;, &#39;CN&#39;), (6, &#39;Mary&#39;, &#39;female&#39;, 18, &#39;1341234&#39;, &#39;USA&#39;))
()

Copy after login

3. Transaction rollback

Transaction rollback is executed before the data is written to the database, so the transaction rollback Roll conn.rollback() before the instance submits the command conn.commit(). As long as the data is not submitted, it can be rolled back, but after the rollback, the ID will be incremented . Please look at the following example:

Insert 3 pieces of data (note transaction rollback):

import pymysql
#连接数据库zcl
conn=pymysql.connect(host=&#39;127.0.0.1&#39;, user=&#39;root&#39;, passwd=&#39;root&#39;, db=&#39;zcl&#39;)
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute(&#39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&#39;, (&#39;Jack&#39;, &#39;man&#39;, 25, 1351234, "CN"))
reCount=cur.execute(&#39;insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)&#39;, (&#39;Jack2&#39;, &#39;man&#39;, 25, 1351234, "CN"))
reCount=cur.execute(&#39;insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)&#39;, (&#39;Mary&#39;, &#39;female&#39;, 18, 1341234, "USA"))

conn.rollback()      #事务回滚
conn.commit()        #实例提交命令

cur.close()
conn.close()
print(reCount)

Copy after login

Before the command is executed and after the command is executed (Including rollback operation) (note the ID number): The result of not executing the above code is the same as executing the above code!! Because the transaction has been rolled back, the students table will not add data!

mysql> select* from students;+----+------+--------+-----+-------------+------+
| id | name | sex    | age | tel         | nal  |
+----+------+--------+-----+-------------+------+
|  1 | zcl  | man    |  22 | 15622341234 | NULL |
|  2 | alex | man    |  30 | 15622341235 | NULL |
|  5 | Jack | man    |  25 | 1351234     | CN   |
|  6 | Mary | female |  18 | 1341234     | USA  |
+----+------+--------+-----+-------------+------+
4 rows in set

Copy after login

After executing the command (excluding rollback operation): Just comment the 11th line of code above.

mysql> select* from students;+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set

Copy after login

Summary: Although the transaction is rolled back, the ID is still incremented and will not be canceled due to rollback, but this does not affect the consistency of the data (I don’t know the underlying principle~)

4. Insert data in batches

import pymysql
#连接数据库zcl
conn = pymysql.connect(host=&#39;127.0.0.1&#39;, user=&#39;root&#39;, passwd=&#39;root&#39;, db=&#39;zcl&#39;)
#生成游标，当前实例所处状态
cur = conn.cursor()
li = [
    ("cjy","man",18,1562234,"USA"),
    ("cjy2","man",18,1562235,"USA"),
    ("cjy3","man",18,1562235,"USA"),
    ("cjy4","man",18,1562235,"USA"),
    ("cjy5","man",18,1562235,"USA"),
]

#插入数据
reCount = cur.executemany(&#39;insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)&#39;, li)

#conn.rollback()  #事务回滚
conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount)

Copy after login

Output under pycharm: 5

mysql terminal display:

mysql> select* from students;   　  #插入数据前+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set


mysql> mysql> select* from students;　　　#插入数据后+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
| 13 | cjy   | man    |  18 | 1562234     | USA  |
| 14 | cjy2  | man    |  18 | 1562235     | USA  |
| 15 | cjy3  | man    |  18 | 1562235     | USA  |
| 16 | cjy4  | man    |  18 | 1562235     | USA  |
| 17 | cjy5  | man    |  18 | 1562235     | USA  |
+----+-------+--------+-----+-------------+------+
12 rows in set

Copy after login

You should summarize what you have learned in time, some things have been forgotten~_~

The above is the detailed content of How to use python to operate database (mysql). For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Roblox: Grow A Garden - Complete Mutation Guide

3 weeks ago By DDD

Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

How to fix KB5055612 fails to install in Windows 10?

3 weeks ago By DDD

Nordhold: Fusion System, Explained

3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Blue Prince: How To Get To The Basement

1 months ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1664

CakePHP Tutorial

1423

Laravel Tutorial

1318

PHP Tutorial

1269

C# Tutorial

1248

Related knowledge

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

Oracle's Role in the Business World Apr 23, 2025 am 12:01 AM

Oracle is not only a database company, but also a leader in cloud computing and ERP systems. 1. Oracle provides comprehensive solutions from database to cloud services and ERP systems. 2. OracleCloud challenges AWS and Azure, providing IaaS, PaaS and SaaS services. 3. Oracle's ERP systems such as E-BusinessSuite and FusionApplications help enterprises optimize operations.

Explain the purpose of foreign keys in MySQL. Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

Python vs. JavaScript: Development Environments and Tools Apr 26, 2025 am 12:09 AM

Both Python and JavaScript's choices in development environments are important. 1) Python's development environment includes PyCharm, JupyterNotebook and Anaconda, which are suitable for data science and rapid prototyping. 2) The development environment of JavaScript includes Node.js, VSCode and Webpack, which are suitable for front-end and back-end development. Choosing the right tools according to project needs can improve development efficiency and project success rate.

Golang vs. Python: The Pros and Cons Apr 21, 2025 am 12:17 AM

Golangisidealforbuildingscalablesystemsduetoitsefficiencyandconcurrency,whilePythonexcelsinquickscriptinganddataanalysisduetoitssimplicityandvastecosystem.Golang'sdesignencouragesclean,readablecodeanditsgoroutinesenableefficientconcurrentoperations,t

Python vs. C : Understanding the Key Differences Apr 21, 2025 am 12:18 AM

Python and C each have their own advantages, and the choice should be based on project requirements. 1) Python is suitable for rapid development and data processing due to its concise syntax and dynamic typing. 2)C is suitable for high performance and system programming due to its static typing and manual memory management.

Laravel vs. Python (with Frameworks): A Comparative Analysis Apr 21, 2025 am 12:15 AM

Laravel is suitable for projects that teams are familiar with PHP and require rich features, while Python frameworks depend on project requirements. 1.Laravel provides elegant syntax and rich features, suitable for projects that require rapid development and flexibility. 2. Django is suitable for complex applications because of its "battery inclusion" concept. 3.Flask is suitable for fast prototypes and small projects, providing great flexibility.

Compare and contrast MySQL and MariaDB. Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

See all articles