Python + Sqlite

[jinnantonnixx]

I've been doing a project in Python and realised that I'd be better off using a small, simple database instead of the JSON files I'd been using.

 

It had never occurred to me to use SQLite, but looking a little closer it seems to be a really nice, simple database. Zero configuration, no faffing about. Great for simple apps. It even has a front-end (SQLiteman) in Ubuntu.

 

More importantly, its binding are baked into Python.

 

I found a really nice website with some good examples of Python and Sqlite.

SQLite Python tutorial

Edited May 29, 2014 by jinnantonnixx

[jinnantonnixx]

Initial tests are very promising.

From a 2 gigabyte JSON source file, I've distilled the columns I need into an SQLite database.

Now, on the resulting SQLite database, I can do a fairly complex query using wildcard text searches, multiple conditions and sorting on the database (which contains over 1 million records) and the results come back in less than 1 second.

 

Very, very impressive.

Well done, SQLite.

 

 

 

http://www.pythoncentral.io/introduction-to-sqlite-in-python/

Edited June 2, 2014 by jinnantonnixx

[LosOjos]

Thanks for this, will have a read later - one of the lads at the code club I help out at was looking in to SQlite for a Python project he started last week, but he had gone off down the path of Common LISP for maintaining the DB - I'm not sure whether this is because he believes it's the only way, whether he has been learning LISP anyway so just wanted to use it (a good enough reason for a personal project in my opinion) or he just likes a challenge! Either way, it's always handy to read up on these things. Didn't have a lot of time left to help him out with it in the end but said I would take a look once I had gotten my head around LISP. Working at a code club has certainly made me realise how limited my knowledge is!

[jinnantonnixx]

It's the bees knees, I tells ya.

 

Why LISP? Strange choice. Though I shouldn't judge; last time I used it was in uni, and I didn't really have a practical use for it.

 

Saying that, I use a dictionary in Python to hold my data structure, then commit the dictionary keys by their keyname to the SQLite record. It's so simple, it's hard to believe. Python is awesome .I've done a fair bit with dotnet and MS SQL over the years, but this is like a breath of fresh air.

Edited June 2, 2014 by jinnantonnixx

[LosOjos]

It's the bees knees, I tells ya.

 

Why LISP? Strange choice. Though I shouldn't judge; last time I used it was in uni, and I didn't really have a practical use for it.

 

Saying that, I use a dictionary in Python to hold my data structure, then commit the dictionary keys by their keyname to the SQLite record. It's so simple, it's hard to believe. Python is awesome .I've done a fair bit with dotnet and MS SQL over the years, but this is like a breath of fresh air.

 

Like I say, I've no idea! I got the impression he thought you needed to use emacs to administer the SQLite DB, so he was learning some LISP to enable him to code some of the work out. If that's the case, he'll be chuffed to find he can do it all from Python (which he's better than I am with!). On the other hand, he's a bright lad and much like myself at his age, he likes a challenge, so he may have set himself the limitation of using LISP just so that he had a practical use to learn it from - as I say, can't knock that on a personal project (I'd seriously discourage it for an important/time limited project mind you!).

 

I've been dragging myself in to the 21st century lately where coding is concerned and have been looking at things like jQuery, Python and Ruby. I may never have bothered if it weren't for the code club as I was quite happy with my "old fashioned" skills (PHP, .Net, Java etc.), but it quickly became apparent that those 3 were pretty much the be all and end all of coding to the youngsters and I'd never used any of them! I must say, once I got past the lack of explicit typing (messes with my "OCD" like you wouldn't believe) I found Python very enjoyable and why I didn't give jQuery a chance years ago I can't imagine!

[jinnantonnixx]

I use Sqlteman (on Ubuntu) to 'administer' the database, but in reality there's really nothing to administer. Sqliteman is great for quickly developing queries. It's got a few goodies too - it can populate tables with as many dummy records as you want - this is handy for testing.

 

It's a huge dilemma trying to pick a language. I was into .net. That's fine if you're a Microsoft shop, but I see a tide of Linux systems heading our way. I looked at Python and I see it to be a universal language with fantastically powerful list processing capabilities. My Python apps are developed on Ubuntu and they happily run on Windows. Cross-platform - this is a huge plus. I always make sure I stick to the stock 2.7.

Edited June 2, 2014 by jinnantonnixx

[LosOjos]

I use Sqlteman (on Ubuntu) to 'administer' the database, but in reality there's really nothing to administer. Sqliteman is great for quickly developing queries. It's got a few goodies too - it can populate tables with as many dummy records as you want - this is handy for testing.

 

I shall pass on that info (and bookmark this thread...) - though through brief chat on Twitter I believe he just wants to know how Lisp works

 

It's a huge dilemma trying to pick a language. I was into .net. That's fine if you're a Microsoft shop, but I see a tide of Linux systems heading our way. I looked at Python and I see it to be a universal language with fantastically powerful list processing capabilities. My Python apps are developed on Ubuntu and they happily run on Windows. Cross-platform - this is a huge plus. I always make sure I stick to the stock 2.7.

 

Yeah I've always enjoyed programming in .Net for the simple reason that I can create a GUI in minutes and focus on my backend, rather than having to programatically create a GUI then tinker with nudging things a few pixels this way, a few that... but I hate having to have users install a specific version of .Net just to run my applications and all the added overhead using managed code adds. You can pretty much say the same for Java, although it being cross platform is a plus!

 

I love the way Python handles lists and strings ('slices' need implementing in all languages!) and that it's cross platform, but I just can't imagine myself working on a major project with it. I don't know why, I haven't come across a situation whereby I've felt it's lacking anything I need (though I've never done anything more complicated than this with it), it just "feels" like a scripting language somehow... perhaps it's just that explicit typecasting snobbery I still harbour deep inside!

 

I'm trying for about the fifth time to really do something with C++ at the moment as I still see that as kind of the "holy grail" of coding; suitable for just about any [non-web] purpose, on just about any device regardless of OS, kernel or even architecture, there will almost certainly be a compiler for it (so long as you avoid native methods of course...); it's the Swiss army knife of programming languages!

 

Problem is, I always find myself getting frustrated with the GUI coding side and slob back over to .Net/Java... I looked in to VC++ with MFC, but then you're straight back to managed code and bulky prerequisites, which kind of feels like cheating to me.

 

When all is said and done though, I think my biggest problem is I've never taken coding seriously enough; I can code well enough to find my way through most languages (though Lisp came as a shock I must say!) so I just kind of coast along picking up new skills when I need them.