【Python学习】访问数据库

使用MySQL

• 安装
  pip install pymysql

import pymysql

# 打开数据库
conn = pymysql.connect('localhost', 'root', 'root', 'demo')

# 游标对象
cur = conn.cursor()

# -------插入数据--------
sql_i = "INSERT INTO `student` (`uid`, `name`, `code`, `create_date`, `last_date`) VALUES ('uid4', 'dfdsf', '3254645', '2020-03-12', '2020-03-12')"
# -------删除数据--------
sql_d = "DELETE FROM `student` WHERE `uid` = 'uid3' "
# -------更新数据--------
sql_u = "UPDATE student SET name = 'eureka' WHERE uid = 'uid1'"
# -------查询数据--------
sql_q = "SELECT * FROM student"

try:
    # cur.execute(sql_i)
    # cur.execute(sql_d)
    cur.execute(sql_u)
    # 提交事务
    conn.commit()
    print('成功')

    # 查询
    cur.execute(sql_q)
    results = cur.fetchall()  # 查询所有
    # 遍历结果
    for row in results:
        id = row[0]
        uid = row[1]
        name = row[2]
        code = row[3]
        create_date = row[4]
        last_date = row[5]
        print(id, uid, name, code, create_date, last_date)
except Exception as e:
    # 错误回滚
    conn.rollback()
finally:
    # 关闭Cursor和Connection
    conn.close()
    cur.close()

使用Oracle

• 安装 pip install cx_Oracle

import cx_Oracle

# 连接数据库
conn = cx_Oracle.connect('scott/scott@localhost/ORCL')
# 游标对象
cursor = conn.cursor()

cursor.execute("SELECT * FROM DEPT")
rows = cursor.fetchall()  # 得到所有数据集
for row in rows:
    print("%d, %s, %s" % (row[0], row[1], row[2]))  # python3以上版本中print()要加括号用了

print("Number of rows returned: %d" % cursor.rowcount)

cursor.execute("SELECT * FROM DEPT")
while (True):
    row = cursor.fetchone()  # 逐行得到数据集
    if row == None:
        break
    print("%d, %s, %s" % (row[0], row[1], row[2]))

print("Number of rows returned: %d" % cursor.rowcount)

cursor.close()
conn.close()