search

MacHu-GWU / uszipcode-project

USA zipcode programmable database, includes up-to-date census and geometry information.
MIT License
231 stars 49 forks source link

sqlite to postgres #44

Closed ra-esmith closed 3 years ago

ra-esmith commented 3 years ago

Hello, the documentation suggests

"(RECOMMEND) Dump the sqlite database to any relational database like Postgres, MySQL, and inject the database connection info in your application server."

Could you elaborate on what needs to be done ... to use postgres?

Not sure of what code changes one needs to make etc ...

Thanks! Evan

MacHu-GWU commented 3 years ago

@ra-esmith I think this question is out of scope of this library. But I do can give you some solution:

  1. find where you store the .sqlite file.
  2. download dbeaver https://dbeaver.io/download/, it is a universal SQL client
  3. connect to the sqlite database, do select * and dump the result as CSV
  4. connect to your psql, and load from CSV

I am about to allow to use any private SQL database to host the data in next release. Then you just need 


from uszipcode.pkg.sqlalchemy_mate import engine_creator
from uszipcode import SearchEngine

engine = engine_creator.create_postgresql(username, password, host, port, database)
se = SearchEngine(..., engine=engine)

now it uses the psql and skip the sqlite db file download process