为了减少各类配置的痛苦，使用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
