Pymysql基本使用

　　激活pycharm：https://shimo.im/docs/GJTqgHqh6kGYkKKY/read

　　设置抬头行：https://blog.csdn.net/qq_38377523/article/details/84764827

　　设置调整字体大小：https://blog.csdn.net/CaoMei_HuaCha/article/details/86086236

1、pymysql安装（Windows下）

      Windows下可以直接打开cmd执行pip install pymysql进行安装，这里介绍在pycharm里进行安装

      第一步：在pycharm里打开setting：

     第二步：搜索pymysql，选择版本号，点解install package

2、导入pymysql，测试登录

 

#!/usr/bin/env python
# -*-coding:utf8 -*-
import pymysql
# MySQL相关设置
DB_host = '10.10.21.196'
DB_user = 'root'
DB_passwd = '123456'
DB_port = 3306
DB = 'home'
def DB_connect():
    """链接数据库"""
    global conn, cursor
    try:
        conn = pymysql.connect(host=DB_host, user=DB_user, passwd=DB_passwd,port=DB_port,charset='utf8', autocommit=True,db=DB)
        print("\033[1;31;46m Message:连接MySQL成功\033[0m")
    except Exception:
        print("\033[0;31m%s\033[0m" % "Message:连接MYSQL失败")
        exit(5)
    finally:
        conn.close()
DB_connect()

3、查询操作

#!/usr/bin/env python
# -*-coding:utf8 -*-
import pymysql
# 打开数据库连接
db = pymysql.connect(host="10.10.21.196",user="root",password="123456",db="home",port=3306)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 1.查询操作
# 编写sql 查询语句  class 对应我的表名
sql = "select * from home" #home库下的home表
try:
    cursor.execute(sql)  # 执行sql语句
    results = cursor.fetchall()  # 获取查询的所有记录
    print("id", "name", "sex","age","weight")
    # 遍历结果
    for row in results:
        id = row[0]
        name = row[1]
        sex = row[2]
        age = row[3]
        weight = row[4]
        print(id, name,sex,age,weight)
except Exception as a:
    raise a
finally:
    db.close()  # 关闭连接

4、插入操作

#!/usr/bin/env python
# -*-coding:utf8 -*-
import pymysql
db = pymysql.connect(host="10.10.21.196", user="root",password="123456", db="home", port=3306)
'''
CREATE TABLE `bill` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(6) NOT NULL COMMENT '用户姓名',
  `account` varchar(11) NOT NULL COMMENT '银行储蓄账号',
  `saving` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '账户储蓄金额',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
'''
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 插入数据
sql = '''
INSERT INTO bill (name, account, saving) VALUES (%s,%s,%s)
'''
data = (('马云','431294834',100000),
         ('马腾','411294834',100000),
        ('雷军','432294393',120000))
try:
    cursor.executemany(sql,data)
    db.commit()
    # print("\033[1;31;46m Message:连接MySQL成功\033[0m")
    print('\033[1;31;46m Message: 成功插入',cursor.rowcount,'条数据\033[0m')
except:
    db.rollback()
    print("\033[0;31m%s\033[0m" % "Message:sql插入失败!")
db.close()