<p>如果将国家名称作为参数传递给数据检索函数,则可以动态生成表名(请注意<code>execute</code>和<code>print</code>中的<a href="https://www.python.org/dev/peps/pep-0498/" rel="nofollow noreferrer">f-string</a>参数):</p>
<h2>初稿</h2>
<pre class="lang-py prettyprint-override"><code>def print_CountryData(country):
conn = sqlite3.connect('FinalProjectDatabase.sqlite3')
cur = conn.cursor()
cur.execute(f"SELECT SUM(AirPollutionLevel) FROM {country}Data WHERE AirPollutant = 'PM10'")
sumVal = cur.fetchone()[0]
print(f"{country} {sumVal}")
# example call:
for country in ('France', 'Germany', 'Italy', 'Malta', 'Poland'):
print_CountryData(country)
</code></pre>
在构建查询字符串时,出于安全原因,在简单的字符串函数中,您自己在<a href="https://docs.python.org/3/library/sqlite3.html" rel="nofollow noreferrer">sqlite3 documentation</a>中被阻止,在您完全控制实际参数的情况下,我认为它是安全的。p>
<p>这个答案调整了<a href="https://stackoverflow.com/a/66208134/2932052">great answer given by forpas</a>的求和,但拒绝将重复移动到SQL。它还显示了与python的集成和输出格式</p>
<h2>MRE样式版本</h2>
<p>这是我第一个答案的改进版本,转换为<a href="https://stackoverflow.com/help/minimal-reproducible-example"><strong>Minimal, Reproducible Example</strong></a>并与输出相结合。此外,还进行了一些性能改进,例如只打开数据库一次</p>
<pre class="lang-py prettyprint-override"><code>import sqlite3
import random # to simulate actual pollution values
# Countries we have data for
countries = ('France', 'Germany', 'Italy', 'Malta', 'Poland')
# There is one table for each country
def tableName(country):
return country+'Data'
# Generate minimal version of tables filled with random data
def setup_CountryData(cur):
for country in countries:
cur.execute(f'''CREATE TABLE {tableName(country)}
(AirPollutant text, AirPollutionLevel real)''')
for i in range(5):
cur.execute(f"""INSERT INTO {tableName(country)} VALUES
('PM10', {100*random.random()})""")
# Get sum up pollution data for each country
def print_CountryData(cur):
for country in countries:
cur.execute(f"""SELECT SUM(AirPollutionLevel) FROM
{tableName(country)} WHERE AirPollutant = 'PM10'""")
sumVal = cur.fetchone()[0]
print(f"{country:10} {sumVal:9.5f}")
# For testing, we use an in-memory database
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
setup_CountryData(cur)
# The functionality actually required
print_CountryData(cur)
</code></pre>
<p>样本输出:</p>
<pre class="lang-none prettyprint-override"><code>France 263.79430
Germany 245.20942
Italy 225.72068
Malta 167.72690
Poland 290.64190
</code></pre>
<p>在没有实际尝试的情况下,通常很难评估解决方案。这就是为什么StackOverflow上的提问者不断被鼓励以这种方式提问的原因:这使得有人更可能理解并解决问题<em>快速</em></p>