I have created a database using sqlite within python 2.7, and loaded the data into the pandas dataframe as below. What I'm trying to do is I would like to print the result as "The cities that are warmest in July are: Istanbul, Ankara, Izmir, Bursa". The code that I have written in Python is as below:
我在python 2.7中使用sqlite创建了一个数据库,并将数据加载到pandas数据帧中,如下所示。我想要做的是我想将结果打印为“7月最温暖的城市是:伊斯坦布尔,安卡拉,伊兹密尔,布尔萨”。我用Python编写的代码如下:
import sqlite3 as lite
import pandas as pd
con = lite.connect("project_warmest.db")
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cities;")
cur.execute("DROP TABLE IF EXISTS weather;")
cur.execute("CREATE TABLE cities (name text, region text)")
cur.execute("CREATE TABLE weather (city text, warm_month text, average_high integer)")
cur.execute("INSERT INTO cities VALUES('Istanbul', 'Marmara')")
cur.execute("INSERT INTO cities VALUES('Ankara', 'Ic Anadolu')")
cur.execute("INSERT INTO cities VALUES('Izmir', 'Ege')")
cur.execute("INSERT INTO cities VALUES('Antalya', 'Akdeniz')")
cur.execute("INSERT INTO cities VALUES('Bursa', 'Marmara')")
cur.execute("INSERT INTO weather VALUES('Istanbul', 'July',24)")
cur.execute("INSERT INTO weather VALUES('Ankara', 'July',21)")
cur.execute("INSERT INTO weather VALUES('Izmir', 'July',27)")
cur.execute("INSERT INTO weather VALUES('Antalya', 'August',30)")
cur.execute("INSERT INTO weather VALUES('Bursa', 'July',23)")
cur.execute("SELECT city FROM weather INNER JOIN cities ON name = city WHERE warm_month = 'July'")
rows = cur.fetchall()
cols = [desc[0] for desc in cur.description]
df = pd.DataFrame(rows, columns = cols)
print "The cities that are warmest in July are: %s, " %df.iloc[0]["city"]
1 个解决方案
#1
You could join array of elements from df["city"]
like
你可以加入来自df [“city”]的元素数组
In [53]: print "The cities warmest in July are: %s" % ', '.join(df["city"].values)
The cities warmest in July are: Istanbul, Ankara, Izmir, Bursa
', '.join(df["city"].values)
-- this will return a comma-separated string.
','。join(df [“city”]。values) - 这将返回逗号分隔的字符串。
Also, you could use pd.read_sql()
or pd.read_sql_query
to directly read the sql results to dataframe.
此外,您可以使用pd.read_sql()或pd.read_sql_query直接将sql结果读取到dataframe。
In [54]: pd.read_sql("SELECT city FROM weather INNER JOIN cities ON name = city"
....: " WHERE warm_month = 'July'", con)
Out[54]:
city
0 Istanbul
1 Ankara
2 Izmir
3 Bursa
#1
You could join array of elements from df["city"]
like
你可以加入来自df [“city”]的元素数组
In [53]: print "The cities warmest in July are: %s" % ', '.join(df["city"].values)
The cities warmest in July are: Istanbul, Ankara, Izmir, Bursa
', '.join(df["city"].values)
-- this will return a comma-separated string.
','。join(df [“city”]。values) - 这将返回逗号分隔的字符串。
Also, you could use pd.read_sql()
or pd.read_sql_query
to directly read the sql results to dataframe.
此外,您可以使用pd.read_sql()或pd.read_sql_query直接将sql结果读取到dataframe。
In [54]: pd.read_sql("SELECT city FROM weather INNER JOIN cities ON name = city"
....: " WHERE warm_month = 'July'", con)
Out[54]:
city
0 Istanbul
1 Ankara
2 Izmir
3 Bursa