[BUG]OperationalError: no such table: countries about geograpy3 HOT 28 CLOSED

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=[]"

from geograpy3.

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

from geograpy3.

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

from geograpy3.

@kruttikanadig certainly so

from geograpy3.

Working on fixing this issue as shown in this commit a7a2514

from geograpy3.

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....

from geograpy3.

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.

from geograpy3.

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

from geograpy3.

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

from geograpy3.

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

from geograpy3.

Yes

from geograpy3.

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.

from geograpy3.

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

from geograpy3.

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!

from geograpy3.

@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' ?

from geograpy3.

@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

from geograpy3.

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

from geograpy3.

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.

from geograpy3.

@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".

from geograpy3.

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.

from geograpy3.

@dprice-int The home directory is found here:

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.

from geograpy3.

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

from geograpy3.

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!

from geograpy3.

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

from geograpy3.

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

from geograpy3.

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.

from geograpy3.

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

from geograpy3.

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

from geograpy3.