2

I am creating a simple python program that needs to search a somewhat large database ( ~40 tables, 6 Million or so rows all together ).

Currently, I use MySQLdb to query my local MySQL database then I have some other python function that work with the data and returns some statistics and other stuff. I would like to share this with others that do not want to construct their own database. At this point the database is used for queries only.

How best can I share the database and python program as a "package". Do I have to give up on the SQL method and switch to some sort of text file database or is there an easier way... sqlite maybe?

If the answer is sqlite how do I go about exporting my current SQL database to the sqlite database? Is there any gotchas I should know about?

Currently I use simple SELECT quarries with a few WHERE statements to locate the data I need. I am afraid that if I switched to text based database I would end up having to write a large amount of code to make these queries.

Thank you in advance for any suggestions.

EDIT

So I wrote my little python program with an sqlite3 database and it works perfectly.

I ended up using using a shell script called mysql2sqlite.sh found here to convert my MySQL database to sqlite. It worked flawlessly.

I only had to change 2 lines of python code. Awesome.

My little program runs in osx, windows and linux (ubuntu and redhat) without any changes or hassle. Thanks for the advise!

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Keith
  • 361
  • 1
  • 4
  • 18
  • SO the main selling point is the bundle of software AND data? If so, i would suggest SQLite3. It's included with the Standard-Python distribution and with simple SQL-Statements chance is you don't even have to alter your existing SQL-querries. – Don Question Nov 08 '12 at 20:15
  • Yes I would like the software and data bundled! SQLite3 sound like a wonderful option if I dont have to heavily alter my sql code. Do you have any advise on creating/exporting a SQLite3 database that can be shared? – Keith Nov 08 '12 at 20:19
  • I would use [SQLAlchemy](http://www.sqlalchemy.org/) as a database abstraction layer and then it is trivial to target multiple database backends, including SQLite for the embedded case. – Pedro Romano Nov 08 '12 at 20:19
  • SQLAlchemy is a great ORM, but sometimes less is more. From the outset i don't think the OP does know/did work with SQLAlchemy and why add the additional layer of abstraction if a simple s&r or regex will be sufficient to port from mysql to sqlite? – Don Question Nov 08 '12 at 20:23
  • how will the db be shared? How static is the data? Would this be better served with a hosted db? – gebuh Nov 08 '12 at 20:39
  • I will probably have to update the data annually or semi annually. I do not think I have the resources or currently the knowledge to properly host a db for others use. I have not thought of how I would share the db I imagined it being available at a ftp server or something. I had hopped that writing the code in python would eliminate a lot of the porting issues. – Keith Nov 08 '12 at 21:17
  • Actualy, you could create a repository on bitbucket or github and checkin/push periodically sql-dumps. This way you could even integrate selected contribution from others or allow them to fork the data easily. – Don Question Nov 08 '12 at 22:16

1 Answers1

1

Converting your database could be as easy as an sql-dump and then an import, depending on the complexity of your db. See this post for strategies and alternatives.

Community
  • 1
  • 1
Don Question
  • 10,018
  • 4
  • 32
  • 50