☰

在 BASH W10 中运行调用 SQL 的 Python

tag-icon • tag-icon linux bash python sql
在 BASH W10 中运行调用 SQL 的 Python

尝试运行这个Python文件 python pid_info.py 12345 ，它看起来像

#!/usr/bin/env python
import subprocess
import sys, getopt

# add if -b or -e then look for username/email like etc... 
# figure out how to store the db creds in separate file 
class color:
   PURPLE = '\033[95m'
   CYAN = '\033[96m'
   DARKCYAN = '\033[36m'
   BLUE = '\033[94m'
   GREEN = '\033[92m'
   YELLOW = '\033[93m'
   RED = '\033[91m'
   BOLD = '\033[1m'
   UNDERLINE = '\033[4m'
   FLASH = '\033[0.5m'
   END = '\033[0m'

# DB info:
host = 
db=
user=
password=
# take the argument provided by user
UN=str(sys.argv[1])    
# SQL query to return user info + role
f_statement1 = """ set nocount on; set ansi_warnings off; 
SELECT 
pl.placement_id PID, pl.placement_name, p.partner_name Publisher, pc.description Platform_client, pit.description +'/'+ dt.description  Integration_Device
FROM placement pl 
JOIN partner p ON pl.partner_id = p.partner_id 
JOIN platform_client pc ON p.platform_client_id = pc.platform_client_id
JOIN placement_integration_type_assoc pita ON pl.placement_id = pita.placement_id 
JOIN placement_integration_type pit ON pita.placement_integration_type_id = pit.placement_integration_type_id
JOIN device_type dt ON pl.device_type_id = dt.device_type_id 
WHERE pit.active=1
AND pita.active=1 AND pl.placement_id = """ + str(UN)

f_statement2 = """ set nocount on; set ansi_warnings off; 
SELECT 
pl.max_ad_duration Seconds, c.abbreviation Country,
CASE WHEN passback_allowed=0 THEN 'GUARANTEED' ELSE 'PASSBACK' END AS Buy_Type, 
CASE WHEN pl.skippable=0 THEN 'Non-Skippable' ELSE 'Skippable' END AS Skippable,
CASE WHEN pl.active=1 THEN 'ACTIVE' ELSE 'NOT_ACTIVE' END AS Status
FROM placement pl 
JOIN country c ON pl.country_id = c.country_id
WHERE   pl.placement_id =""" + str(UN)

f_statement3 = """ set nocount on; set ansi_warnings off;
SELECT url_expression FROM AN_MAIN..placement_domain_whitelist
WHERE active=1 and placement_id =""" + str(UN)

# run the first query
print('\n')
print(color.UNDERLINE + color.BOLD + "Results for PID " + str(UN) + ":" + color.END)
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement1, "-Y","30", "-s", "|" ])
print('\n')
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement2, "-Y","30", "-s", "|" ])
print('\n')
print(color.UNDERLINE + color.BOLD + "Whitelist for PID " + str(UN) + ":" + color.END)
print('\n')
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement3, "-Y","30", "-s", "|" ])
print('\n')

input ()

当我这样做时，我收到错误

Results for PID 12345:
Traceback (most recent call last):
  File "pid_info.py", line 57, in <module>
    results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement1, "-Y","30", "-s", "|" ])
  File "/usr/lib/python2.7/subprocess.py", line 523, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/usr/lib/python2.7/subprocess.py", line 711, in __init__
    errread, errwrite)
  File "/usr/lib/python2.7/subprocess.py", line 1343, in _execute_child
    raise child_exception
OSError: [Errno 2] No such file or directory

我需要在这里做出什么改变？

答案1

sqlcmdPython 脚本在环境变量中列出的任何目录中都找不到的环境中运行PATH。

确保包含调用脚本之前所在的PATH目录，或使用其完整路径。sqlcmdsqlcmd

我确信有适合 Python 的 SQL 库，它允许您在 Python 代码中创建数据库连接，而无需使用某些外部二进制文件。这还允许您执行不易受到 SQL 注入攻击的准备好的语句

您永远不会清理UN变量，这意味着可以使用以下命令调用脚本"12345; DROP DATABASE 'mydatabase';"

相关内容