数据库操作

2019年12月10日

image.png

mysql -uroot -pwy123456

* 创建数据库 */
CREATE DATABASE IF NOT EXISTS MyDB;

use MyDB;

/* 用户表 */
CREATE TABLE IF NOT EXISTS user (
name varchar(20), /*name */
userid int, /* useid */
PRIMARY KEY (userid));

/* 插入数据 */
INSERT INTO user VALUES('Tom',1);
INSERT INTO user VALUES('Ben',2);

一.查询：

1.有条件查询

import pymysql

# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wy123456',
                             database='MyDB',
                             charset='utf8')

try:
    # 2\. 创建游标对象
    with connection.cursor() as cursor:
        # 3\. 执行SQL操作
        # 3.1 通过元组绑定
        # sql = 'select name,userid from user where userid > %s'
        # cursor.execute(sql, [0])

        # 3.2 通过字典绑定
        sql = 'select name,userid from user where userid > %(id)s'
        cursor.execute(sql, {'id': 0})

        # 4\. 提取结果集
        result_set = cursor.fetchall()

        for row in result_set:
            print('id:{0} - name:{1}'.format(row[1], row[0]))

        # cursor.close()

    # with代码块结束 5\. 关闭游标

finally:
    # 6\. 关闭数据连接
    connection.close()

image.png

2.无条件查询

# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wy123456',
                             database='MyDB',
                             charset='utf8')

try:
    # 2\. 创建游标对象
    with connection.cursor() as cursor:

        # 3\. 执行SQL操作
        # 3.1 通过元组绑定
        # sql = 'select name,userid from user where userid > %s'
        # cursor.execute(sql, [0])

        # 3.2 通过字典绑定
        sql = 'select max(userid) from user'
        cursor.execute(sql)

        # 4\. 提取结果集
        row = cursor.fetchone()
        if row is not None:
            print('最大用户id:{0}'.format(row[0]))

        # cursor.close()

    # with代码块结束 5\. 关闭游标

finally:
    # 6\. 关闭数据连接
    connection.close()

image.png

二.修改操作

1.增

import pymysql

# 查询最大用户Id
def read_max_userid():
    # 1\. 建立数据库连接
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='wy123456',
                                 database='MyDB',
                                 charset='utf8')

    try:
        # 2\. 创建游标对象
        with connection.cursor() as cursor:
            # 3\. 执行SQL操作
            sql = 'select max(userid) from user'
            cursor.execute(sql)
            # 4\. 提取结果集
            row = cursor.fetchone()
            if row is not None:
                print('最大用户Id ：{0}'.format(row[0]))
                return row[0]
        # with代码块结束 5\. 关闭游标
    finally:
        # 6\. 关闭数据连接
        connection.close()

# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wy123456',
                             database='MyDB',
                             charset='utf8')

# 查询最大值
maxid = read_max_userid()
try:
    # 2\. 创建游标对象
    with connection.cursor() as cursor:
        # 3\. 执行SQL操作
        sql = 'insert into user (userid, name) values (%s,%s)'
        nextid = maxid + 1
        name = 'Tony' + str(nextid)
        affectedcount = cursor.execute(sql, (nextid, name))

        print('影响的数据行数：{0}'.format(affectedcount))
        # 4\. 提交数据库事物
        connection.commit()

    # with代码块结束 5\. 关闭游标

except pymysql.DatabaseError:
    # 4\. 回滚数据库事物
    connection.rollback()
finally:
    # 6\. 关闭数据连接
    connection.close()

image.png

2.改

import pymysql

# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wy123456',
                             database='MyDB',
                             charset='utf8')

try:
    # 2\. 创建游标对象
    with connection.cursor() as cursor:
        # 3\. 执行SQL操作
        sql = 'update user set name = %s where userid > %s'
        affectedcount = cursor.execute(sql, ('Tom', 2))
        print('影响的数据行数：{0}'.format(affectedcount))

        # 4\. 提交数据库事物
        connection.commit()

    # with代码块结束 5\. 关闭游标

except pymysql.DatabaseError as e:
    # 4\. 回滚数据库事物
    connection.rollback()
    print(e)
finally:
    # 6\. 关闭数据连接
    connection.close()

image.png

3删

import pymysql

# 查询最大用户Id
def read_max_userid():
    # 1\. 建立数据库连接
    connection = pymysql.connect(host='localhost',
                                 user='root',
                                 password='wy123456',
                                 database='MyDB',
                                 charset='utf8')

    try:
        # 2\. 创建游标对象
        with connection.cursor() as cursor:
            # 3\. 执行SQL操作
            sql = 'select max(userid) from user'
            cursor.execute(sql)

            # 4\. 提取结果集
            row = cursor.fetchone()
            if row is not None:
                print('最大用户Id ：{0}'.format(row[0]))
                return row[0]

        # with代码块结束 5\. 关闭游标

    finally:
        # 6\. 关闭数据连接
        connection.close()

# 1\. 建立数据库连接
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='wy123456',
                             database='MyDB',
                             charset='utf8')

# 查询最大值
maxid = read_max_userid()

try:
    # 2\. 创建游标对象
    with connection.cursor() as cursor:
        # 3\. 执行SQL操作
        sql = 'delete from user where userid = %s'
        affectedcount = cursor.execute(sql, (maxid))
        print('影响的数据行数：{0}'.format(affectedcount))

        # 4\. 提交数据库事物
        connection.commit()

    # with代码块结束 5\. 关闭游标

except pymysql.DatabaseError:
    # 4\. 回滚数据库事物
    connection.rollback()
finally:
    # 6\. 关闭数据连接
    connection.close()

image.png

三.其他NoSQL数据库存储

image.png image.png

import dbm

with dbm.open('mydb', 'c') as db:
    db['name'] = 'tony'  # 更新数据
    print(db['name'].decode())  # 取出数据

    age = int(db.get('age', b'18').decode())  # 取出数据
    print(age)

    if 'age' in db:  # 判断是否存在age数据
        db['age'] = '20'  # 或者 b'20'

    del db['name']  # 删除name数据

image.png

如果您发现本文对你有所帮助，如果您认为其他人也可能受益，请把它分享出去。