python数据库操作(mssql和oracle)

先安装mysql官方python程序。

#!/usr/bin/python
# -*- coding: UTF-8 -*-
#pip install mysql-connector
import mysql.connector

# 打开数据库连接 
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="123321",
  database="test"
)
# 使用cursor()方法获取操作游标 
mycursor = mydb.cursor()
'''
#删除语句 execute(sql)后，
sql = "DELETE FROM sites WHERE name = 'stackoverflow'"
mycursor.execute(sql)
mydb.commit()
'''
#批量插入 
'''
sql = "INSERT INTO sites (name, url) VALUES (%s, %s)"
val = [
  ('Google', 'https://www.google.com'),
  ('Github', 'https://www.github.com'),
  ('Taobao', 'https://www.taobao.com'),
  ('stackoverflow', 'https://www.stackoverflow.com/')
]
 
mycursor.executemany(sql, val)
 
mydb.commit()    # 数据表内容有更新，必须使用到该语句
'''

mycursor.execute("select * from zongbiao")
 
myresult = mycursor.fetchall()     # fetchall() 获取所有记录
 
for x in myresult:
  print(x)

oracle的连接方法

注意下载 instantclient-basic-windows.x64-11.2.0.4.0，解压
https://www.oracle.com/cn/database/technology/winx64soft.html
再到 环境变量里面添加 path =C:\jhui\instantclient_11_2
python 3.7.9 window10 连接服务器是linux64_oracle

# -*- coding: UTF-8 -*-
#pip install cx_Oracle
#用途：操作oracle数据库demo
import cx_Oracle
import sys
import os

user = "Myuser"
passwd = "Myuser"
listener = '172.14.0.79:1521/orcl'
conn = cx_Oracle.connect(user, passwd, listener)
print(conn)
cursor = conn.cursor()
sql = "select * from fundasset t where custid <75030000030"
 
#一次取一条数据,row为元组数据
cursor.execute(sql)
while (1):
    row = cursor.fetchone()
    if row == None:
        break
    print(row)
print("--------------------------------------")
#一次取所有数据,rows为元组列表数据
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
    print(row)
 
#支持对数据库的插入、更新和删除操作。输入操作SQL，执行无返回。
def other_operation(sql):
    cursor.execute(sql)
    conn.commit()
    print(sql)
 
cursor.close()
conn.close()