python通过ODBC连接神通数据库

1、安装神通数据库

2、安装python

3、安装pyodbc

pip3 install pyodbc-5.2.0-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

注：pyodbc要和python版本相对应

4、安装unixodbc

5、配置神通数据库ODBC数据源

6、示例代码如下

#!/usr/bin/python3

import pyodbc

# 配置ODBC连接参数
dsn = 'odsn'  # 数据源名称（DSN）
username = 'sysdba'  # 用户名
password = 'szoscar55'  # 密码

# 连接字符串
connection_string = f'DSN={dsn};UID={username};PWD={password};'

# 连接到数据库
try:
    conn = pyodbc.connect(connection_string)
    print("连接成功！")
    cursor = conn.cursor()

    ###创建表
    table_create_sql = """
    CREATE TABLE if not exists abc(column1 varchar(10),column2 varchar(10),column3 varchar(10));"""
    cursor.execute(table_create_sql)
    conn.commit()
    print("创建成功!")

    # 插入数据（INSERT）
    insert_sql = '''
    INSERT INTO abc (column1, column2, column3)
    VALUES (?, ?, ?)
    '''
    values_to_insert = ([('value1', 'value2', 'value3'),('value4', 'value5', 'value6')])
    cursor.executemany(insert_sql, values_to_insert)
    #cursor.execute(insert_sql, values_to_insert)
    conn.commit()  # 提交事务
    print("插入成功！")

    # 查询数据（READ）
    select_sql = 'SELECT * FROM abc'
    cursor.execute(select_sql)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # 更新数据（UPDATE）
    update_sql = '''
    UPDATE abc
    SET column2 = ?
    WHERE column1 = ?
    '''
    new_value = 'new_value2'
    condition_value = 'value1'  # 假设这是你要更新的行的条件值
    cursor.execute(update_sql, (new_value, condition_value))
    conn.commit()  # 提交事务
    print("更新成功！")

    # 删除数据（DELETE）
    delete_sql = '''
    DELETE FROM abc
    WHERE column1 = ?
    '''
    condition_value_for_delete = 'value1'  # 假设这是你要删除的行的条件值
    cursor.execute(delete_sql, (condition_value_for_delete,))
    conn.commit()  # 提交事务
    print("删除成功！")

except pyodbc.Error as e:
    print(f"数据库操作失败: {e}")

finally:
    # 关闭游标和连接
    if cursor:
        cursor.close()
    if conn:
        conn.close()
        print("连接已关闭。")