如何将函数中附加的值用单引号括起来,然后插入到Python中的SQL字符串中

huangapple go评论82阅读模式
英文:

How to enclose appended value in single quotes from function into a sql string in python

问题

我试图将来自函数的附加值封装到Python中的SQL字符串中,但不知何故无法做到。

getname 返回 Puru

def getValue(getname):
    sql = '''SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '''+getname

我想以单引号形式返回它:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'Puru'
英文:

Here i am trying to enclose an appended value coming from function into a sql string in python
but somehow i am not able to it.

getname is returning Puru

    def getValue(getname):
    sql= '''SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '''+getname

I want to return it in this form in single quotes

    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'Puru'

答案1

得分: 0

以下是要翻译的内容:

这里有三种实现你想要的方式:

  1. 字符串拼接
sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" + getname + "'"
  1. 字符串格式化
sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{}'".format(getname)
  1. 格式化字符串(需要 Python 3.6+)
sql = f"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{getname}'"
英文:

Here are three ways to achieve what you want:

string concatenation

sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '"+getname+"'"

string.format()

sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{}'".format(getname)

format string (requires python 3.6+)

sql = f"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{getname}'"

答案2

得分: 0

除了str.format()之外,Python还提供了string模块中的Template类。

Template提供了更简单的字符串替换功能。与普通的基于%的替换不同,模板支持基于$的替换。

例如:

from string import Template
sql = Template("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
               "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '$getname'")
sql.substitute(getname='Puru')

$getname表示一个替换占位符,与映射键**"getname"**匹配。但我建议您使用str.format()方法,因为它在Python中更易读且被广泛用于字符串格式化。

英文:

For your understanding other than str.format() python also provides Template class from string module.
Templates provide simpler string substitutions in python. Instead of the normal %-based substitutions, Templates support $-based substitutions.
<br>for ex:

&gt;&gt;&gt; from string import Template
&gt;&gt;&gt; sql = Template(&quot;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES &quot;
                   &quot;WHERE TABLE_TYPE = &#39;BASE TABLE&#39; AND TABLE_CATALOG = &#39;$getname&#39;&quot;)
&gt;&gt;&gt; sql.substitute(getname=&#39;Puru&#39;)
&quot;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = &#39;BASE TABLE&#39; AND TABLE_CATALOG = &#39;Puru&#39;&quot;

The $getname names a substitution placeholder matching a mapping key of "getname". But I would recommend you to use str.format() method which is more readable and widely used for string formatting in python.

答案3

得分: -1

You need to replace the &#39;&#39;&#39; with &quot;. Otherwise Python will not know how to interpret the &#39; in the middle. If you don't want the line to get too long, you can wrap it in parentheses:

sql = (&quot;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES &quot;
       &quot;WHERE TABLE_TYPE = &#39;BASE TABLE&#39; AND TABLE_CATALOG = &quot;) + getname

Edit: I have to point out the dangers of SQL injection and creating these queries on the fly. There are numerous libraries that will do it for you in a safe manner. But I have a feeling/hope this won't end up in production code anyway.

英文:

You need to replace the &#39;&#39;&#39; with &quot;. Otherwise Python will not know how to interpret the &#39; in the middle. If you don't want the line to get too long, you can wrap in in parentheses:

sql = (&quot;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES &quot;
       &quot;WHERE TABLE_TYPE = &#39;BASE TABLE&#39; AND TABLE_CATALOG = &quot;) + getname

Edit: I have to point out the dangers of SQL injection and creating these queries on the fly. There are numerous libraries that will do it for you in a safe manner. But I have a feeling/hope this won't end up in production code anyway.

huangapple
  • 本文由 发表于 2020年1月4日 00:20:00
  • 转载请务必保留本文链接:https://go.coder-hub.com/59581874.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定