ODBC接口实现增删改查等操作
使用pyodbc
打开cmd,输入命令pip install pyodbc
预处理板块
try:
print('是否有预处理操作(存储过程or修改内容or触发器or...\n\t', end='')
if(input() == 'yes'):
str = '''合法SQL语句即可'''
curs.execute(str)
conn.commit()
print('预处理已成功执行!')
except Exception:
traceback.print_exc()
traceback打印报错信息
try:
curs.execute(str)
conn.commit()
except Exception:
traceback.print_exc()
完整源码
import pyodbc
import traceback
# 连接
print('Connecting...\n')
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=PC-20190716TTIK;DATABASE=学生选课;UID=sa;PWD=123456')
# 建立cursor
curs = conn.cursor()
# 预处理(存储过程or修改内容or触发器or...
try:
print('是否有预处理操作(存储过程or修改内容or触发器or...\n\t', end='')
if(input() == 'yes'):
str = '''CREATE TRIGGER trig_t ON TEACHER AFTER INSERT
AS
BEGIN
DECLARE @ERROR VARCHAR(20)
SET @ERROR=(SELECT 部门ID号 FROM inserted)
IF @ERROR NOT IN(SELECT 部门ID号 FROM DEPARTMENT)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
END''' # 创建INSERT触发器,如果向教师表中插入记录时,检查该记录的院系编号在表中是否存在。如果不存在,则不允许插入
curs.execute(str)
conn.commit()
print('预处理已成功执行!')
except Exception:
traceback.print_exc()
# 打印表
def show(db):
print('-------------------------------------------------------')
try:
db = curs.execute("select * from dbo.{}".format(db)).fetchall()
# tplt = "{0:^8}\t{1:^8}\t{2:^8}\t{3:^8}"
# print(tplt.format('班级号', '班主任姓名', '教室', '系编号'))
for i in db:
# print(tplt.format(i[0], i[1], i[2], i[3]))
for j in i:
print(j, end='\t\t')
print('\n', end='')
except Exception:
traceback.print_exc()
print('-------------------------------------------------------')
# 查询
def select(str, b):
print('-------------------------------------------------------')
try:
result = curs.execute(str).fetchall()
if(b == 5):
tplt = "{0:^8}\t{1:^8}\t{2:^8}\t{3:^8}\t{4:^8}"
for i in result:
print(tplt.format(i[0], i[1], i[2], i[3], i[4]))
elif(b == 4):
tplt = "{0:^8}\t{1:^8}\t{2:^8}\t{3:^4}"
for i in result:
print(tplt.format(i[0], i[1], i[2], i[3]))
elif(b == 3):
tplt = "{0:^8}\t{1:^8}\t{2:^8}"
for i in result:
print(tplt.format(i[0], i[1], i[2]))
elif(b == 2):
tplt = "{0:^8}\t{1:^8}"
for i in result:
print(tplt.format(i[0], i[1]))
elif(b == 1):
tplt = "{0:^8}"
for i in result:
print(tplt.format(i[0]))
except Exception:
traceback.print_exc()
print('-------------------------------------------------------')
# 修改、增添、删除
def orders(str):
try:
curs.execute(str)
conn.commit()
except Exception:
traceback.print_exc()
while(1):
print('''\nHello! Let me show you the orders:
\t1 -- print TABLE
\t2 -- select\t查询
\t3 -- alter\t修改
\t4 -- add\t增添
\t5 -- delete\t删除
\t# -- quit\t退出''')
print('Your order: ', end='')
a = input()
if(a == '#'):
break
elif(a == '1'):
print('-- TABLE: ', end='')
str = input()
show(str)
elif(a == '2'):
print('-- 列数:', end='')
b = int(input())
print('-- -- SQL语句: ', end='')
str = input()
select(str, b)
elif(a == '3'):
print('-- SQL语句: ', end='')
str = input()
orders(str)
elif(a == '4'):
print('-- SQL语句: ', end='')
str = input()
orders(str)
elif(a == '5'):
print('-- SQL语句: ', end='')
str = input()
orders(str)