[BUG]OperationalError: no such table: countries

[jadhvank]

Describe the bug
A clear and concise description of what the bug is.
[OperationalError: no such table: countries]

`---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
in
1 import geograpy
2 url = 'https://en.wikipedia.org/wiki/2012_Summer_Olympics'
----> 3 places = geograpy.get_geoPlace_context(url=url)

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/init.py in get_geoPlace_context(url, text, debug)
22 PlaceContext: the place context
23 '''
---> 24 places=get_place_context(url, text, labels=Labels.geo, debug=debug)
25 return places
26

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/init.py in get_place_context(url, text, labels, debug)
44 e.find_entities(labels=labels)
45 places=e.places
---> 46 pc = PlaceContext(places)
47 pc.setAll()
48 return pc

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/places.py in init(self, place_names, setAll, correctMisspelling)
30 self.places = self.normalizePlaces(place_names)
31 if setAll:
---> 32 self.setAll()
33
34 def str(self):

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/places.py in setAll(self)
85 Set all context information
86 '''
---> 87 self.set_countries()
88 self.set_regions()
89 self.set_cities()

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/places.py in set_countries(self)
96 countries = []
97 for place in self.places:
---> 98 country=self.getCountry(place)
99 if country is not None:
100 countries.append(country.name)

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/geograpy/locator.py in getCountry(self, name)
1160 params=(name,name,)
1161 country = None
-> 1162 countryRecords=self.sqlDB.query(query,params)
1163 if len(countryRecords)==1:
1164 country=Country.fromRecord(countryRecords[0])

~/.conda/envs/WikiCOVID/lib/python3.7/site-packages/lodstorage/sql.py in query(self, sqlQuery, params)
181 cur=self.c.cursor()
182 if params is not None:
--> 183 query = cur.execute(sqlQuery,params)
184 else:
185 query = cur.execute(sqlQuery)

OperationalError: no such table: countries`

To Reproduce
Steps to reproduce the behavior:

1. Run the example on local, remote, colab, jupyter notebook

Expected behavior
A clear and concise description of what you expected to happen.
The codes should results list of countries

Screenshots
If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

• OS: MAC / UBUNTU / COLAB
• Python Version 3.9 / 3 / ?

Additional context
Add any other context about the problem here.

[WolfgangFahl]

Thanks for the detailed bug report. It looks like the SQLLite database is not in the place where it should be. It should be downloaded and extracted automatically.

Could you please check whether
$HOME/.geograpy3/locations.db
is available in your environment?

What Operating System are you using - you are mentioning three different ones?
Also it looks like you are using python conda virtualization environment which might also effect what is going on.

In any case the error message is most probably misleading.

[stong1108]

I have the same experience and error message. I am not running this in a virtual environment, and my machine is Mac OS, running on Python 3.8.6

[WolfgangFahl]

Please try removing your $HOME/.geograpy3/locations.db and thus force the update of the file. Does the error persist then?

[stong1108]

It says that this doesn't exist. I do have a $HOME/.geograpy3/locations.db, should I delete that instead?

[somnathrakshit]

Yes

[WolfgangFahl]

You'll also find the relevant database at https://github.com/somnathrakshit/geograpy3/wiki

[jadhvank]

There is locations.db under .geograpy3 folder
and through the environment and code, I can reach the file.
Currently, I am using mac os and python version is 3.8.8.

[jadhvank]

The file was empty so I followed the link @WolfgangFahl mentioned and downloaded it and it works!
Thanks!!

[WolfgangFahl]

There is an improved version of the download procedure which will download if the file is empty see https://github.com/WolfgangFahl/ConferenceCorpus/blob/main/corpus/datasources/download.py

[variablenerd]

Could you please make the issue title more specific? I was searching for a solution to this error for a long time before stumbling upon this page. Thanks!

[WolfgangFahl]

@kruttikanadig certainly so

[zorroxt1118]

@WolfgangFahl ,thank you for your kindly explaination, I have the same issue, and I tried to follow the procedure, but I still have some basic questions.

1. About the location: '$HOME/.geograpy3/locations.db'.
   I am using MacOS, I am not sure whether '$HOME' means (as in my case)
   ' /Users/XXX/opt/anaconda3/lib/python3.8/site-packages/' (this is where I found folders for geograpy)

2. I found a folder named 'geograpy' and 'geograpy3-0.2.1.dist-info', but I can not find the folder '.geograpy3' in the above direction. Is it in a different direction?

3. should I extract the two database files in the 'geograpy3-0.2.1.dist-info' ?

[WolfgangFahl]

@zorroxt1118 - thx for your question
just type

echo $HOME
``` in a terminal and you'll see the result e.g. 
/Users/...
likewise you can do a 
```bash
ls -l $HOME/.geograpy3

which should have content such as:

ls -l $HOME/.geograpy3
total 555264
-rw-r--r--     1 wf  staff  217006080 19 Aug 11:23 locations.db
-rw-r--r--     1 wf  staff   65271260 19 Aug 07:36 locations.db.gz

if neither the locations.db.gz file nor the locations.db file is there you might want to fix this.

also

pip list | grep geograpy
geograpy3                     0.2.1
pip3 list | grep geograpy
geograpy3                     0.2.1

should show whether you have a recent version. If not you might want to upgrade

[nkhan076]

The file was empty so I followed the link @WolfgangFahl mentioned and downloaded it and it works! Thanks!!

Hi, I am facing the same problem. I deleted locations.db from ~/.geograpy3, downloaded locations.db from the wiki link and placed it inside ~/.geograpy3 folder. But it is not working. I am using python 3.6

[dprice-int]

I have the same issue using Windows/Visual Studio/geograpy3. I applied the newer download script as supplied by @WolfgangFahl to utils.py but it made no difference. I tried downloading the location.db file but no matter the location the error continued to occur.

[WolfgangFahl]

@dprice-int thank you for your comment. What Windows Version are you using and where does your HOME directory point to? You find this information if you run a cmd.exe command line environment and type "env".

[dprice-int]

Hi @WolfgangFahl - I am using Windows 10. In Visual Studio I created a Python solution/project (located here C:\Code\datawarehouse\Solution\GeographyTextParsing), created a virtual environment (located here C:\Code\datawarehouse\Solution\GeographyTextParsing*env*) and installed geograpy3 using pip. It is python 3.7.

I'm sorry but there is no env command using Windows command prompt. Is HOME meant to be an environment variable? Here's a few more things that may shed some light on the configuration:

print(os.environ.get("PYTHONPATH")) in the script returns C:\Code\datawarehouse\Solution\GeographyTextParsing\ whereas print(os.environ.get("HOME")) returns "None". Windows has environment variables. I created a variable called "HOME" and set it to C:\Code\datawarehouse\Solution\GeographyTextParsing. This made no difference. I added C:\Code\datawarehouse\Solution\GeographyTextParsing\ to the "PATH" environment variable and this also made no difference.

[somnathrakshit]

@dprice-int The home directory is found here:

geograpy3/geograpy/locator.py

Line 154 in fcea745

home = str(Path.home())

Can you try running the following to locate your home directory?

from pathlib import Path
print(str(Path.home()))

Under your home directory, place the database inside .geograpy3 directory. Let me know how this turns out.

[dprice-int]

Thanks for the information - sorry I'm not a very proficient Python user! I ran the script and found the result was "C:\Users<my account name>". I moved the directory there and it didn't initially work but I realised that I needed both the db & db.gz files and then it worked!

Thanks very much for your assistance and @WolfgangFahl.

Now I have a question about usage but I'll create something in the discussions area....

[jkoo9]

Hi, I'm using Windows 10/Visual studio to run the code. I did put the locations.db.gz in my home .geograpy3 directory as well as deleted the existing locations.db file but I'm still getting the error. I'm also using python 3.8.5

[lisawylie]

Hi, I'm experiencing the same issue using Codespaces/VSCode running python 3.9. The installation doesn't appear to be installing the locations.db or db.gz in the directory, but adding them manually as detailed above also doesn't resolve the problem.

---

OperationalError Traceback (most recent call last)
/workspaces/global_biogeography/notebooks/geograpy3_test.py in
6 url = 'https://en.wikipedia.org/wiki/2012_Summer_Olympics_torch_relay'
----> 7 places = geograpy.get_geoPlace_context(url=url)

~/.local/lib/python3.9/site-packages/geograpy/init.py in get_geoPlace_context(url, text, debug)
22 PlaceContext: the place context
23 '''
---> 24 places=get_place_context(url, text, labels=Labels.geo, debug=debug)
25 return places
26

~/.local/lib/python3.9/site-packages/geograpy/init.py in get_place_context(url, text, labels, debug)
44 e.find_entities(labels=labels)
45 places=e.places
---> 46 pc = PlaceContext(places)
47 pc.setAll()
48 return pc

~/.local/lib/python3.9/site-packages/geograpy/places.py in init(self, place_names, setAll, correctMisspelling)
30 self.places = self.normalizePlaces(place_names)
31 if setAll:
---> 32 self.setAll()
33
34 def str(self):

~/.local/lib/python3.9/site-packages/geograpy/places.py in setAll(self)
85 Set all context information
86 '''
---> 87 self.set_countries()
88 self.set_regions()
89 self.set_cities()

~/.local/lib/python3.9/site-packages/geograpy/places.py in set_countries(self)
96 countries = []
97 for place in self.places:
---> 98 country=self.getCountry(place)
99 if country is not None:
100 countries.append(country.name)

~/.local/lib/python3.9/site-packages/geograpy/locator.py in getCountry(self, name)
1138 params=(name,name,)
1139 country = None
-> 1140 countryRecords=self.sqlDB.query(query,params)
1141 if len(countryRecords)==1:
1142 country=Country.fromRecord(countryRecords[0])

~/.local/lib/python3.9/site-packages/lodstorage/sql.py in query(self, sqlQuery, params)
184 cur=self.c.cursor()
185 if params is not None:
--> 186 query = cur.execute(sqlQuery,params)
187 else:
188 query = cur.execute(sqlQuery)

OperationalError: no such table: countries

and my directory looks like this (newly installed):
@lisawylie ➜ /workspaces/global_biogeography (main ✗) $ cd ~/.local/lib/python3.9/site-packages/geograpy
@lisawylie ➜ ~/.../lib/python3.9/site-packages/geograpy $ ls
init.py pycache data extraction.py labels.py locator.py places.py utils.py wikidata.py
@lisawylie ➜ ~/.../lib/python3.9/site-packages/geograpy $

Thanks for looking at this, the package looks really useful!

[tholzheim]

Thanks for pointing it out.
There seems to be an issue with some functions in the Locator class not checking if the database is present.
I will fix it ASAP.

I tested it in clean VM and got the same error when using geograpy.get_geoPlace_context(). A workaround to load the database is to use geograpy.locateCity() once since it ensures that the db is loaded. As shown below it downloaded the db and I was able to use geograpy.get_geoPlace_context().

>>> str(geograpy.get_geoPlace_context(text="Berlin is the capitol of germany"))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/__init__.py", line 24, in get_geoPlace_context
    places=get_place_context(url, text, labels=Labels.geo, debug=debug)
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/__init__.py", line 46, in get_place_context
    pc = PlaceContext(places)
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/places.py", line 32, in __init__
    self.setAll()
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/places.py", line 87, in setAll
    self.set_countries()
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/places.py", line 98, in set_countries
    country=self.getCountry(place)
  File "/home/vmt14/.local/lib/python3.8/site-packages/geograpy/locator.py", line 1140, in getCountry
    countryRecords=self.sqlDB.query(query,params)
  File "/home/vmt14/.local/lib/python3.8/site-packages/lodstorage/sql.py", line 186, in query
    query = cur.execute(sqlQuery,params)
sqlite3.OperationalError: no such table: countries

>>> str(geograpy.locateCity("Berlin"))
Downloading /home/vmt14/.geograpy3/locations.db.gz from https://raw.githubusercontent.com/wiki/somnathrakshit/geograpy3/data/locations.db.gz ... this might take a few seconds
unzipping /home/vmt14/.geograpy3/locations.db from /home/vmt14/.geograpy3/locations.db.gz
'Berlin (DE-BE(Berlin) - DE(Germany))'

>>> str(geograpy.get_geoPlace_context(text="Berlin is the capitol of germany"))
"countries=['Germany', 'United States of America', 'South Africa', 'Denmark']\nregions=[]\ncities=['Berlin']\nother=[]"

[lisawylie]

OK, cool, I'll try that out, thank you!

[lisawylie]

Confirming the workaround of forcing a download by using geograpy.locateCity() worked for me, thank you!

[somnathrakshit]

One of the reasons why this download problem occurs mostly in Windows is that the scripts keyword in setup.py does not work in Windows, as mentioned here.

[somnathrakshit]

Working on fixing this issue as shown in this commit a7a2514

[somnathrakshit]

Please use the latest version (0.2.3) from PyPi to fix this error.
v0.2.2...v0.2.3

[WolfgangFahl]

Will close this as mostly a Visual Studio Code/Microsoft Windows bug