Linux下使用SQLServer | Word count: 676 | Reading time: 3min | Post View:
Linux 下使用 SQLServer
#数据存储/SQLServer #操作系统/Linux
1. 说明
SQL Server 是由 Microsoft 开发和推广的关系数据库管理系统。本文介绍在
Ubuntu 系统下,SQL Server 服务端及客户端的安装,基本命令及如何使用
python 访问数据。
2. 安装
由于 SQLServer 不在默认安装的软件源之中,在 Ubuntu 16.04
上,需要先加入其软件源,安装后再进行一些配置
(1) SQLServer 服务器端
1 2 3 4 5 6 7 8 9 $ wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - $ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list) " $ sudo apt-get update $ sudo apt-get install -y mssql-server $ sudo apt-get -f install $ sudo /opt/mssql/bin/mssql-conf setup $ systemctl stop mssql-server $ sudo /opt/mssql/bin/mssql-conf set-collation $ systemctl enable mssql-server && systemctl start mssql-server
(2) SQLServer 命令行工具
1 2 3 4 5 6 $ sudo sh -c "curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -" $ sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-serverxenial main > /etc/apt/sources.list.d/sql-server.list" $ $sudo sh -c "echo deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main >>/etc/apt/sources.list.d/sql-server.list" $ sudo apt-get update $ sudo apt-get install -y mssql-tools-y $ dpkg -L mssql-tools
(3) SQLServer 的 Python 支持包
1 $ sudo pip install pymssql
3. SQLServer 基本命令
1 $ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 密码
(1) 建库
1 2 > create database testme> go
(2) 看当前数据库列表
1 2 > select * from SysDatabases> go
(3) 看当前数据表
1 2 3 > use 库名 > select * from sysobjects where xtype= 'u' > go
(4) 看表的内容
1 2 > select * from 表名;> go
4. Python 程序访问 SQLServer
数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 import pymssqlserver = 'localhost' user = 'sa' password = 密码 database = 'testme' conn = pymssql.connect(server, user, password, database) cursor = conn.cursor() cursor.execute(""" IF OBJECT_ID('persons', 'U') IS NOT NULL DROP TABLE persons CREATE TABLE persons ( id INT NOT NULL, name VARCHAR(100), salesrep VARCHAR(100), PRIMARY KEY(id) ) """ )cursor.executemany( "INSERT INTO persons VALUES (%d, %s, %s)" , [(1 , 'John Smith' , 'John Doe' ), (2 , 'Jane Doe' , 'Joe Dog' ), (3 , 'Mike T.' , 'Sarah H.' )]) conn.commit() cursor.execute('SELECT * FROM persons WHERE salesrep=%s' , 'John Doe' ) row = cursor.fetchone() while row: print ("ID=%d, Name=%s" % (row[0 ], row[1 ])) row = cursor.fetchone() conn.close()
5. 参考
Ubuntu下安装配置SQLSERVER2017
如何在Linux上安装和使用MS
SQL Server
Python连接SQL
Server数据库 - pymssql使用基础
sqlcmd介绍
python连接sqlserver和MySQL实现增删改查