python学习笔记(1) - python操作mysql数据库【持续更新中...】

最新推荐文章于 2023-02-13 12:00:28 发布

HαLlus

最新推荐文章于 2023-02-13 12:00:28 发布

阅读量419

点赞数

分类专栏： python 文章标签： mysql python

本文链接：https://blog.csdn.net/Astartia/article/details/109294055

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章目录

使用pymysql操作数据库

使用pymysql操作数据库

01 - 基本操作步骤

建立数据库连接
创建cursor
在cursor上执行SQL语句
关闭cursor和连接

import pymysql

# 建立数据库连接
con = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = '9875321',
    database = 'world'
)

# 创建cursor
cursor = con.cursor() 
print(cursor)

# ===============
# 执行SQL语句
# ===============

# 关闭cursor
cursor.close()
# 关闭connection
con.close()

输出结果：

<pymysql.cursors.Cursor object at 0x000001C61B54E1F0>

1-1 SQL查询

# 在cursor上执行SQL语句

## 1 - 查询
sql_select = '''
    select * from country limit 5;
'''
cursor.execute(sql_select)
## 使用fetchall()获取查询结果
data = cursor.fetchall() # 查询数据以复合tuple的形式存储
for item in data:
    print(item) # 每条查询数据为一个tuple

输出结果：

('ABW', 'Aruba', 'North America', 'Caribbean', 193.0, None, 103000, 78.4, 828.0, 793.0, 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 129, 'AW')
('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.0, 1919, 22720000, 45.9, 5976.0, None, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF')
('AGO', 'Angola', 'Africa', 'Central Africa', 1246700.0, 1975, 12878000, 38.3, 6648.0, 7984.0, 'Angola', 'Republic', 'JosÃ© Eduardo dos Santos', 56, 'AO')
('AIA', 'Anguilla', 'North America', 'Caribbean', 96.0, None, 8000, 76.1, 63.2, None, 'Anguilla', 'Dependent Territory of the UK', 'Elisabeth II', 62, 'AI')
('ALB', 'Albania', 'Europe', 'Southern Europe', 28748.0, 1912, 3401200, 71.6, 3205.0, 2500.0, 'ShqipÃ«ria', 'Republic', 'Rexhep Mejdani', 34, 'AL')

1-2 SQL插入数据


sql_insert = '''
    insert into country values ('KLB', 'Klbania', 'Europe', 'Southern Europe', 28748.0, 1912, 3401200, 71.6, 3205.0, 2500.0, 'ShqipÃ«ria', 'Republic', 'Rexhep Mejdani', 34, 'AL');
'''
# 使用cursor执行sql语句
cursor.execute(sql_insert)

此时从本地数据库上查询无相关记录：

mysql> select * from country where `Code`='KLB';
Empty set (0.00 sec)

注意：只使用cursor执行插入语句，不会对本地数据库有所影响，需要使用con.commit()命令提交事务，此时插入数据的操作才能在本地数据库执行。完整脚本如下：

sql_insert = '''
    insert into country values ('KLB', 'Klbania', 'Europe', 'Southern Europe', 28748.0, 1912, 3401200, 71.6, 3205.0, 2500.0, 'ShqipÃ«ria', 'Republic', 'Rexhep Mejdani', 34, 'AL');
'''
cursor.execute(sql_insert)
try:
    con.commit()
except:
    print('Error')

回到本地数据库查询结果如下：

mysql> select * from country where `Code`='KLB';
+------+---------+-----------+-----------------+-------------+-----------+------------+----------------+---------+---------+------------+----------------+----------------+---------+-------+
| Code | Name    | Continent | Region          | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP     | GNPOld  | LocalName  | GovernmentForm | HeadOfState    | Capital | Code2 |
+------+---------+-----------+-----------------+-------------+-----------+------------+----------------+---------+---------+------------+----------------+----------------+---------+-------+
| KLB  | Klbania | Europe    | Southern Europe |    28748.00 |      1912 |    3401200 |           71.6 | 3205.00 | 2500.00 | Shqip??ria | Republic       | Rexhep Mejdani |      34 | AL    |
+------+---------+-----------+-----------------+-------------+-----------+------------+----------------+---------+---------+------------+----------------+----------------+---------+-------+
1 row in set (0.00 sec)

1-3 SQL修改数据

下面要把国家代号为KLB的条目对应的Region字段修改为Antarctic，运行脚本前查询如下：

mysql> select region from country where `Code`='KLB';
+-----------------+
| region          |
+-----------------+
| Southern Europe |
+-----------------+
1 row in set (0.00 sec)

下面运行如下脚本：

sql_update = '''
    update country set Region='Antarctic' where Code='KLB';
'''
# 执行sql
cursor.execute(sql_update)

# 提交事务
try:
    con.commit()
except:
    print('Error')

运行后再查询相关信息，region字段的值已经修改。

mysql> select region from country where `Code`='KLB';
+-----------+
| region    |
+-----------+
| Antarctic |
+-----------+
1 row in set (0.00 sec)

1-4 SQL删除数据

sql_delete = '''
    delete from country where Code='KLB';
'''
# 执行sql
cursor.execute(sql_delete)
# 提交事务
try:
    con.commit()
except:
    print('Error')

再查询本地数据库，相关数据信息已经删除：

mysql> select * from country where `Code`='KLB';
Empty set (0.00 sec)

实例

I. 复制查询结果到其他数据库

import pymysql

# 第一步：建立数据库连接

## 连接到原数据库
con = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = '9875321',
    database = 'world'
)
## 连接到目标数据库
con_target = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = '9875321',
    database = 'test'
)

## 创建cursor
cursor = con.cursor() 
cursor_target = con_target.cursor()

#========================
# 第二步：查询相关信息

## 查询原数据库的建表语句
sql_create_table = '''
    show create table country;
'''
cursor.execute(sql_create_table)
c = cursor.fetchone()
ct = c[1]
print(ct)

## 查询数据
sql_querry = '''
    select * from country order by SurfaceArea desc limit 100;
'''
cursor.execute(sql_querry)
q = cursor.fetchall()

#=======================
# 第三步：将信息复制到目标数据库

## 在目标数据库中创建表格
check_sql = 'drop table if exists country;'

### 在目标数据库cursor上执行建表SQL语句
cursor_target.execute(check_sql) # 避免重复创建产生错误
cursor_target.execute(ct+';')

### 向目标数据库连接提交事务
try:
    con_target.commit()
    print('Table Created Successfully!')
except:
    print('Error')
    
## 循环执行插入语句
for item in q:
    sql_insert = 'insert into country values%s;'% str(item)
    ### 处理可能发生的SQL执行异常
    try:
        cursor_target.execute(sql_insert)
    except:
        print('InsertionError:' + sql_insert)

## 执行完毕统一提交事务
try:
    con_target.commit()
except:
    print('Commission_Error')

脚本运行结果如下：

Table Created Successfully!
InsertionError:insert into country values('ATA', 'Antarctica', 'Antarctica', 'Antarctica', 13120000.0, None, 0, None, 0.0, None, 'Â–', 'Co-administrated', '', None, 'AQ');
InsertionError:insert into country values('SDN', 'Sudan', 'Africa', 'Northern Africa', 2505813.0, 1956, 29490000, 56.6, 10162.0, None, 'As-Sudan', 'Islamic Republic', 'Omar Hassan Ahmad al-Bashir', 3225, 'SD');
InsertionError:insert into country values('GRL', 'Greenland', 'North America', 'North America', 2166090.0, None, 56000, 68.1, 0.0, None, 'Kalaallit Nunaat/GrÃ¸nland', 'Part of Denmark', 'Margrethe II', 917, 'GL');
InsertionError:insert into country values('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.0, 1919, 22720000, 45.9, 5976.0, None, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF');
InsertionError:insert into country values('SOM', 'Somalia', 'Africa', 'Eastern Africa', 637657.0, 1960, 10097000, 46.2, 935.0, None, 'Soomaaliya', 'Republic', 'Abdiqassim Salad Hassan', 3214, 'SO');
InsertionError:insert into country values('IRQ', 'Iraq', 'Asia', 'Middle East', 438317.0, 1932, 23115000, 66.5, 11500.0, None, 'Al-Â´Iraq', 'Republic', 'Saddam Hussein al-Takriti', 1365, 'IQ');
InsertionError:insert into country values('ESH', 'Western Sahara', 'Africa', 'Northern Africa', 266000.0, None, 293000, 49.8, 60.0, None, 'As-Sahrawiya', 'Occupied by Marocco', 'Mohammed Abdel Aziz', 2453, 'EH');
InsertionError:insert into country values('BLR', 'Belarus', 'Europe', 'Eastern Europe', 207600.0, 1991, 10236000, 68.0, 13714.0, None, 'Belarus', 'Republic', 'Aljaksandr LukaÂšenka', 3520, 'BY');
InsertionError:insert into country values('PRK', 'North Korea', 'Asia', 'Eastern Asia', 120538.0, 1948, 24039000, 70.7, 5332.0, None, 'Choson Minjujuui InÂ´min Konghwaguk (Bukhan)', 'Socialistic Republic', 'Kim Jong-il', 2318, 'KP');

查询本地数据库如下：

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| country        |
+----------------+
1 row in set (0.00 sec)

mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|       91 |
+----------+
1 row in set (0.00 sec)

在数据库中显示数据小于100条，正好对应了SQL执行异常的数量。这里可以单独在数据库中执行相关语句：

mysql> insert into country values('ATA', 'Antarctica', 'Antarctica', 'Antarctica', 13120000.0, None, 0, None, 0.0, None, 'Â-', 'Co-administrated', '', None, 'AQ');
ERROR 1054 (42S22): Unknown column 'None' in 'field list'

结果显示，我们通过cursor.fetchall()创建SQL插入语句的时候，查询结果为null的值被自动替换为None，因此需要将插入语句部分的代码修改如下：


## 循环执行插入语句
for item in q:
    sql_insert = "insert into country values%s;"% str(item)
    sql_insert = sql_insert.replace("None","null") # 替换相关字符
    ### 处理可能发生的SQL执行异常
    try:
        cursor_target.execute(sql_insert)
    except:
        print('InsertionError:' + sql_insert)

先将目标数据库内容还原为空，再运行修改后的脚本，可以得到如下结果：

mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

HαLlus

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
python学习笔记(1) - python操作mysql数据库【持续更新中...】

使用pymysql操作数据库基本操作步骤#mermaid-svg-GYz5ctyol5KbIRoA .label{font-family:'trebuchet ms', verdana, arial;font-family:var(--mermaid-font-family);fill:#333;color:#333}#mermaid-svg-GYz5ctyol5KbIRoA .label text{fill:#333}#mermaid-svg-GYz5ctyol5KbIRoA .node rect,#
复制链接

扫一扫