搭建SQL Server 2019

为了减少各类配置的痛苦，使用docker来搭建SQL Server数据库。

拉取镜像

docker pull mcr.microsoft.com/mssql/server:2019-latest

配置数据库

在宿主机中执行

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 -h sql1 \
   -d mcr.microsoft.com/mssql/server:2019-latest

# change the SA password
sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd  -S localhost -U SA -P '<YourStrong@Passw0rd>'  -Q 'ALTER LOGIN SA WITH PASSWORD="<YourNewStrong@Passw0rd>"'

sudo docker exec -it sql1 "bash"

此时会进入docker container 内部

# 进入sqlcmd
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourNewStrong@Passw0rd>"

创建用户

# 创建数据库
create database sqltest
# 创建登录凭证
create login sqltest with password=‘docker_test’
#创建登录数据库
create user sqltest for login sqltest with default_schema=dbo

测试连接

在容器外测试连接

from sqlalchemy import create_engine
import urllib

if __name__ == "__main__":
    server = '<yourIPaddress>,1433'
    database = "sqltest"
    user = "sqltest"
    pwd = "<YourNewStrong@Passw0rd>"
    remote_con_str = (
        "DRIVER={ODBC Driver 17 for SQL Server};SERVER="
        + server
        + ";DATABASE="
        + database
        + ";UID="
        + user
        + ";PWD="
        + pwd
    )
    params = urllib.parse.quote_plus(remote_con_str)
    engine = create_engine(
        "mssql+pyodbc:////?odbc_connect=%s" % params, fast_executemany=True
    )
    d = pd.read_sql(sql="select 1", con=engine)
    print(d)

正常连接应该控制台会打印

0 1

referrence

1. 微软官方文档