sqlserver python脚本操作

1、odbc驱动安装

1.1安装odbc 源

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo


删除odbc相关驱动
rpm -qa | grep ODBC
yum remove unixODBC unixODBC-devel
yum remove unixODBC-utf16 unixODBC-utf16-devel
yum install -y msodbcsql17
yum install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo yum install -y unixODBC-devel

1.2、调整驱动配置文件

vim /etc/odbcinst.ini
将名字改成与脚本名字对应，这里改成了 SQL Server

image.png

2. python插入数据

# -*- coding: utf-8 -*-
import pyodbc
import random
import time

# 建立与数据库的连接
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=10.123.123.123;DATABASE=test;UID=123;PWD=123')

# 打开一个游标
cursor = conn.cursor()

# 关闭自动提交，开启一个事务
cursor.execute("SET IMPLICIT_TRANSACTIONS ON")
cursor.execute("BEGIN TRANSACTION")

# 定义要插入的数据总数
total_records = 1000000

# 定义批次大小和等待时间
batch_size = 500
wait_time = 0.001  # 10 毫秒

# 循环插入数据
for i in range(1, total_records+1):
    # 生成随机数据
    id = i
    name = "user{}".format(i)
    age = random.randint(18, 60)
    email = "user{}@example.com".format(i)
    address = "address{}".format(i)
    salary = random.randint(10000, 50000)
    education_level = random.choice(["high school", "college", "master", "phd"])
    last_updated = time.strftime("%Y-%m-%d %H:%M:%S")
    field9 = "字段9"
    field10 = "字段10"
    field11 = "字段11"
    field12 = "字段12"
    field13 = "字段13"
    field14 = "字段14"
    field15 = "字段15"
    field16 = "字段16"
    field17 = "字段17"
    field18 = "字段18"
    field19 = "字段19"
    field20 = "字段20"

    # 构造插入语句和参数
    sql = "INSERT INTO dbo.\"user\"  VALUES (?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    params = (id, name, age, email, address, salary, education_level, last_updated,field9,field10,field11,field12,field13,field14,field15,field16,field17,field18,field19,field20)

    # 执行插入操作，并等待一段时间
    cursor.execute(sql, params)
    
    # time.sleep(wait_time)


    # 如果批次大小达到了指定的数量，就提交一次事务
    if i % batch_size == 0:
        cursor.execute("commit transaction")
        conn.commit()
        print("已插入 {} 条数据".format(i))
        
        # 重新开启一个事务
        cursor.execute("BEGIN TRANSACTION")

# 提交最后一个事务，并关闭游标和连接
cursor.execute("commit transaction")
conn.commit()
cursor.close()
conn.close()
print("已完成数据插入")

3. 查询语句

import pyodbc
import random
import time

# 建立与数据库的连接
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=x.x.x.x;UID=asd,PWD=asddasd')
cursor = conn.cursor()
print('链接成功')


cursor.execute('select distinct logisticsOrderGuid from dbo.CT_DC_LogisticsOrderEntryDetail')
values = cursor.fetchall()
print(values)
cursor.close()

常见问题

这是驱动没安装好

Error: ('01000',"[01000] [unixODBC][Driver Manager]
    Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

解决

1、确定/etc/odbcinst.ini驱动名称是否与脚本驱动名称一致
2、确定驱动lib 包是否存储，如果不存在重装驱动即可