Python mariadb

Python mariadb

一、python mariadb使用实例

先安装PyMySQL库

pip install PyMySQL

1.连接数据库如下

import pymysql

# 连接配置信息
conn = pymysql.connect(host='127.0.0.1',
                       port = 3306,
                       user = 'root',
                       password = '123',
                       db = 'hghtest',
                       charset = 'utf8mb4',
                       cursorclass = pymysql.cursors.DictCursor)

# 创建连接
cur = conn.cursor()
cur.execute("SELECT id1,amount1 FROM t1")
for r in cur:
    print(r)
cur.close()
conn.close()

数据库中的数据

2.插入数据

import pymysql

# 连接配置信息
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'db': 'hghtest',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}


conn = pymysql.connect(**config)

try:
    with conn.cursor() as cursor:
        # 执行sql语句，插入记录
        sql = "insert into t1(id1,amount1) values(%s, %s)"
        cursor.execute(sql, (520, 1314))
        # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
        conn.commit()
finally:
    conn.close()

3.查询数据

import pymysql

# 连接配置信息
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'db': 'hghtest',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}


conn = pymysql.connect(**config)

try:
    with conn.cursor() as cursor:
        # 执行sql语句，插入记录
        sql = "SELECT * FROM t1;"
        cursor.execute(sql)
        for r in cursor:
            print(r)
        # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
        conn.commit()
finally:
    conn.close()

二、实现增删改查

1.输入数据一组并插入查询

import pymysql

id1 =input("请输入id: " )
amount1 =input("请输入总数: ")
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'db': 'hghtest',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

conn = pymysql.connect(**config)

try:
    with conn.cursor() as cursor:
        sql = "insert into t1(id1,amount1) values(%s,%s);"
        sql1 = "SELECT * from t1;"
        cursor.execute(sql,(id1,amount1))
        cursor.execute(sql1)
        for read in cursor:
            print(read)
    conn.commit()
finally:
    conn.close()

2.修改数据

import pymysql

id1 =input("请输入id: " )
amount1 =input("请输入总数: ")
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'db': 'hghtest',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

conn = pymysql.connect(**config)

try:
    with conn.cursor() as cursor:
        sql = "update t1 set id1 =%s ,amount1 =%s where id1=2020;"
        sql1 = "SELECT * from t1;"
        cursor.execute(sql,(id1,amount1))
        cursor.execute(sql1)
        for read in cursor:
            print(read)
    conn.commit()
finally:
    conn.close()

3.删除数据

import pymysql

id1 =input("请输入id: " )
#amount1 =input("请输入总数: ")
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '123',
    'db': 'hghtest',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
}

conn = pymysql.connect(**config)

try:
    with conn.cursor() as cursor:
        sql = "delete from t1 where id1 =%s;"
        sql1 = "SELECT * from t1;"
        cursor.execute(sql,(id1))
        cursor.execute(sql1)
        for read in cursor:
            print(read)
    conn.commit()
finally:
    conn.close()

这里的删除数据还不够完善，我就不完善了。

（判断输入的id是否在数据库里面）