使用Python与SQL Server进行连接和交互

使用Python与SQL Server进行连接和交互

import pymssql

def conn():
    # 建立连接
    connect = pymssql.connect(host='host_name',
                              user='user_name',
                              password='password_name',
                              database='database_name')  # 服务器名，用户名，密码，数据库名

    if connect:
        print('Successfully connect!')

    cursor = connect.cursor()  # 创建游标对象，对SQL Server进行的SQL语句操作都需通过游标对象
    cursor.execute('select * from table_name')  # 执行SQL语句，进行相应操作
    result = cursor.fetchall()  # 获取所有查询结果；返回一个由元组组成的列表，每一行数据就是一个元组，如果没有数据可获取，则返回一个空列表
    
    cursor.close()  # 关闭游标
    connect.close()  # 关闭连接，释放资源

    return result

if __name__ == '__main__':
    result = conn()

    for row in result:
        print(row)

获取数据的几种方式：

• cursor.fetchone() # 以元组形式返回一条记录，如果没有数据，则返回None
• cursor.fetchmany(num) # 以列表形式返回多条记录，列表每个元素都是一个元组，包含一条记录
• cursor.fetchall() # 以列表形式返回所有记录，列表每个元素都是一个元组，包含一条记录

游标cursor如同C语言中的指针一样，每次操作都会移动到一定位置，如果不指定它返回到开头，那么就会沿着当前位置继续下一次的移动。

cursor = connect.cursor()
cursor.execute('select * from table_name')
result1 = cursor.fetchone()  # 返回表中第一行数据
result2 = cursor.fetchmany(3)  # 游标从第二行开始，返回表中的2、3、4行数据

cursor = connect.cursor()
cursor.execute('select * from table_name')
result1 = cursor.fetchone()  # 返回表中第一行数据
cursor.execute('select * from table_name')  # 让游标回到开头
result2 = cursor.fetchmany(3)  # 返回表中的1、2、3行数据

常见的SQL语句：

• CREATE：用于创建新的数据库或表
  CREATE DATABASE database_name # 创建一个名为database_name的数据库
  CREATE TABLE student (id INT, name VARCHAR(20), age INT, sex VARCHAR(10)) # 创建一个名为student的表，包括id、name、age和sex四个字段

• ALTER：用于修改数据库或表

  ALTER TABLE student ADD COLUMN email VARCHAR(50) # 修改名为student的表，添加一列email字段

• UPDATE：用于更新现有的记录
  UPDATE student SET name=‘Lucy’ WHERE id=1 # 将student表中id为1的记录中的姓名改为Lucy
  UPDATE student SET sex=‘男’ # 不带条件的修改，会将sex列的所有值都修改

• DELETE：用于从表中删除记录
  DELETE FROM student WHERE id=1 # 将表中id为1的记录删除
  DELETE FROM student # 删除表中所有数据

• INSERT：用于将新记录插入到表中
  INSERT INTO student (name, age) VALUES (‘Tom’, 20) # 往student表中插入一条新记录，包含部分列值
  INSERT INTO student VALUES (10, ‘Jack’, 25, ‘男’) # 往student表中插入一条新记录，包含所有列值

• SELECT：用于从表中检索数据，根据条件返回检索结果
  SELECT * FROM student WHERE age > 18 # 从student表中检索所有年龄大于18的记录
  SELECT name, age FROM student # 从student表中检索name、age列

• 查询年龄大于等于20以及年龄小于等于30的记录
  SELECT * FROM student WHERE age >= 20 && age <= 30
  SELECT * FROM student WHERE age >= 20 AND age <= 30
  SELECT * FROM student WHERE age BETWEEN 20 AND 30

• 查询年龄为18、22、25的记录
  SELECT * FROM student WHERE age = 18 OR age = 22 OR age = 25
  SELECT * FROM student WHERE age IN (18, 22, 25)

• 查询英语成绩不为NULL的记录
  SELECT * FROM student WHERE english_score IS NOT NULL

• 模糊查询
  SELECT * FROM student WHERE name LIKE ‘马%’ # 查询姓名第一个字为马的记录
  SELECT * FROM student WHERE name LIKE ‘_斯%’ # 查询姓名第二个字为斯的记录
  SELECT * FROM student WHERE name LIKE ’ ___ ’ # 查询姓名是三个字的记录
  SELECT * FROM student WHERE name LIKE ‘%克%’ # 查询姓名中包含克字的记录

• 去重查询
  SELECT DISTINCT name FROM student # 查询name列，去掉重复值的记录

• 排序查询
  SELECT * FROM student ORDER BY age # 默认升序
  SELECT * FROM student ORDER BY age desc # 降序

连接SQL Server并保存数据：

import pymssql
import csv

def conn():
    # 建立连接
    connect = pymssql.connect(host='192.168.xx.xx',
                              user='xx',
                              password='xxxxxx',
                              database='xx')  # 服务器名，用户名，密码，数据库名

    if connect:
        print('Successfully connect!')

    cursor = connect.cursor()  # 创建游标对象，对SQL Server进行的SQL语句操作都需通过游标对象
    cursor.execute('select * from Mode105')  # 执行SQL语句，进行相应操作
    column_names = [column[0] for column in cursor.description]  # 获取查询结果的列名

    # 创建csv文件，并写入列名
    with open(r'E:/DL_Model/Train/sql_source_data.csv', 'w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        writer.writerow(column_names)

        # 遍历查询结果，并写入csv文件
        for row in cursor.fetchall():
            writer.writerow(row)

    cursor.close()  # 关闭游标
    connect.close()  # 关闭连接，释放资源

if __name__ == '__main__':
    conn()