用python操作SQL Server

平时都不想触碰SQL Server这套东西，最终因为项目原因，有机会用python和SQL Server做了一番联动。

先说一下SQL Server

如果有条件可以使用远程连接或者TeamViewer等工具，操作带GUI的数据库管理器，实施起来可以很省心。可以直接查看结果，模板化查询甚至提示语句错误等。
如果是使用linux本地或者ssh访问SQL Server数据库的，稍微麻烦点。可以使用sqlcmd作为替代工具。后面会介绍。
没有mysql一样的show database;命令，所以显示所有的数据库都比较麻烦。可以使用这条命令：select name from sysobjects where xtype='u'

SQL Server中各个系统表的作用

sysaltfiles 仅在主数据库 保存数据库的文件
syscharsets 仅在主数据库 字符集与排序顺序
sysconfigures 仅在主数据库 配置选项
syscurconfigs 仅在主数据库 当前配置选项
sysdatabases 仅在主数据库 服务器中的数据库
syslanguages 仅在主数据库 语言
syslogins 仅在主数据库 登陆帐号信息
sysoledbusers 仅在主数据库 链接服务器登陆信息
sysprocesses 仅在主数据库 进程
sysremotelogins 仅在主数据库 远程登录帐号
syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型

sql cmd

直接访问数据库：

sqlcmd -S SERVERNAME -U USERNAME -P PASSWORD -d DB

示例sqlcmd -S "127.0.0.1" -U "sa" -P "12345678"

遇到查询操作时提示“对象名无效”的情况：

可能因为没有选择数据库，或者数据库里没有该表。一定要跟上参数-d。

进入执行模式之后可以输入自己的SQL语句。可以连续多条，命令最后以GO语句结尾，是SQL Server的语法。

若要提高性能，请在一个 sqlcmd 会话中执行尽可能多的操作，而不是在一系列会话中来执行这些操作。

输入/输出选项-f codepage | i:codepage[,o:codepage] | o:codepage[,i:codepage]，用于指定输入和输出代码页。 代码页页码是指定已安装的 Windows 代码页的数值。

代码页转换规则：

如果未指定代码页， sqlcmd 会将当前代码页同时用于输入文件和输出文件，除非输入文件为 Unicode 文件，在此情况下无需进行转换。

sqlcmd 自动识别 Big-endian Unicode 和 Little-endian Unicode 输入文件。 如果已指定 -u 选项，输出将始终为 Little-endian Unicode。

如果未指定输出文件，输出代码页将为控制台代码页。 借助此方法，可以在控制台上正确显示输出。

假定多个输入文件具有相同的代码页。 可以将 Unicode 和非 Unicode 输入文件混合在一起。

在命令提示符处输入 chcp 以验证 Cmd.exe 的代码页。

-i input_file[，输入_file2...]

标识包含一批 SQL 语句或存储过程的文件。 可以指定要按顺序读取和处理的多个文件。 文件名之间不要使用任何空格。 sqlcmd 将首先检查所有指定的文件是否都存在。 如果有一个或多个文件不存在， sqlcmd 将退出。 -i 和 -Q/-q 选项是互斥的。

-i C:\<filename>  
-i \\<Server>\<Share$>\<filename>  
-i "C:\Some Folder\<file name>"  

清空表

删除表：

delete from 表名

清除表中的所有数据、保留表结构（ 清除表中所有行，保留表结构、与delete类似）：

truncate table 表名

比delete速度快，效率高，使用的系统和事务日志资源少。

删除表(所有数据及表结构)：

drop table 表名

pymssql

如果使用pymssql操作，基本上就是一把梭。

基本的建立连接

HOST = '127.0.0.1'
USER = 'USERNAME'
PW = 'PASSWORD'
DB = 'DBNAME'
conn = pymssql.connect(host=HOST, user=USER, password=PW, database=DB)
cursor = conn.cursor()

执行SQL语句（查询）：

cursor.execute('SELECT * FROM mytable')
row = cursor.fetchone()
while row:
	row = cursor.fetchone()
conn.close()

在操作执行SQL语句的函数.execute()的时候，传入语句不能临时进行拼接，必须是常量或者格式化的变量。可能是考虑注入等问题，故意设定的。

执行SQL语句（其他）：

cursor.execute("INSERT INTO position VALUES (%s, %s)", (num, address))
conn.commit()

除了查询语句比较特殊，其他的语句一般都需要额外的使用.commit()进行数据提交。如果不提交，可以即便语句执行了，也没有结果返回。此外，连接一开始设定了自动提交属性，可以忽略这条。