Rather then thinking of learning either sqlite or mysql you should consider learning sqlalchemy
, using an orm is much easier as the tables are represented as python objects and once you learn it, its easy to just switch out which database its using and it your code will pretty much stay the same.
The following is how to choose which database to use.
wants to learn about databases, I would suggest not
using sqlachemy. At least not at first. sqlachemy hides a lot of details and makes databases look like collections of classes. While this can be very handy, it would only confuse metulburr
's understanding of how database really work, how they are different
from collections of classes, and why they are useful in the first place. In order to use sqlalchemy properly, you still need to know what's going on underneath. Besides, sqlalchemy should not be used indiscriminately; in simpler applications, it often makes sense to interact with the DB directly rather than get involved in complexities of an ORM. And certain things (eg. views) are actually harder to deal with in sqlalchemy.
metulburr wrote:Ok so i have gotten accustomed to using pickle, shelve, json, for storage (in python).
If those work for you, great. You don't have
to use a relation database to store data. Often, smaller, less structured methods, like the once you have listed, offer a better solution (I would advise against using Pickle, but the others are fine).
metulburr wrote:I am somewhat interested in sqlite3 as it appears to by similar to mysql, and if i was to ever need a large database, i wouldnt have to "learn" it then, but i would already be somewhat accustomed to it.
Yup, this is correct. sqlite is a relational database and share relational concepts and the query language (SQL) with the "big" databases like MySQL, Oracle, Postrgre, MS SQL Server, etc. With programming, the hardest part is learning to program, not learning a particular language. Once you're familiar with programming concepts, picking up new languages is a lot easier. Relational database are like that. Once you understand the underlying concepts, and are comfortable with SQL, learning to use a particular database becomes much easier.
metulburr wrote:these look like a lot of work just to insert and extract data to and from these databases. A lot more so than the shelve module. I know that sqlite3 and mysql can be addressed from any language too. But i dont see the reason for the hassle to go about using it?
They may look daunting now, but once you make the initial leap, using relational databases is not much more work than using any other kind of structured storage. In return for the little bit of extra effort involved, you get a fast, efficient and cross-language storage and a powerful and expressive query language. With server-based databases (pretty much most relational databases, apart form sqlite) you also get the ability to use the same database from multiple clients simultaneously. SQL is actually a very good way of expressing queries (there is a reason why pretty much all relational databases use that), and sometimes an SQL statement is a lot clearer than equivalent object oriented or procedural code. This is why you often see SQL-like constructs appearing in programming languages (the most obvious example is Linq in C#, but also things like list and dict comprehensions in Python).
metulburr wrote:How large is too large for shelve, or pickle when you should switch to a larger database? IS there an in between database between shelve and sqlite3?
How large is a piece of string
? It really depends. I've used sqlite3 for as little as a couple of dozen records. You'll find that some projects use it to store their settings in lieu of the more traditional config files (Firefox is an example). The motivation for using a server-based relational database is often driven by more than just size considerations (e.g. the need to support multiple clients, run complex queries, be accessible from different environments, backup and replication support).
metulburr wrote:Are there other database's out there that work well for beginner database "users"?
sqlite3 is probably your best bet.
metulburr wrote:Like one thing i noticed about these small database files, is you can adjust the size of the obj. Whereas in sqlite, you need to create a table of set amount of size. What happens if one row on a table needs an extra column? Do you need t oseparate it, or can it have a mix match of sizes of coloumn?
Relational databases rely on a rigid schema. This means that the number, types, and even sizes of columns are defined in advance. The data is assumed to fit those schemas. If it doesn't it is reshaped before being added to the database. This rigidity is what allows theses databases to be so fast and efficient, and it also helps dealing with complex queries (since you can make a lot of assumptions without having to verify them, e.g. that a particular field is present).
This works great for some applications, but not others. That is why in recent years a number of alternatives to relational database model have sprung up. They are collectively know as NoSQL database. This can be somewhat misleading as, unlike relational database, they are not really a coherent class. Basically the only thing that they have in common is that they do not rely on SQL or the relational concepts that underpin it. They use vastly different data models (some rely on a schema, others don't, others are in-btween), and use different methods of querying data (which are often closer to traditional programing languages than SQL). You should definitely look into those as well as the SQL-based database, since they both have their uses. A good place to start would probably be redis, since it's relatively simple and comes with a cool online interactive tutorial
. Another popular choice in this area is MongoDB
metulburr wrote:I dont know, i might be biting off more than i can chew right now. Like i am looking at book just describing how to use mysql. I cant beleive a database can be so complex? OF course that is to someone who has never really used a database before.
It sounds like you've just picked a bad place to start. Don't get discouraged! Databases can get very
complicated when used for high-demand, high-performance or complex systems, however they don't have to be. You can often get a lot of benefit out of very simple (sometimes just a single table) databases.
metulburr wrote:Should i even bother learning these databases like sqlite3 or just go to mysql from the start?
I would strongly recommend starting with sqlite3. It is infinitely simpler to set up than MySQL (you don't need to install a server, you don't need to create accounts, you don't need to do anything -- sqlite3 is part of stdlib). It will allow you to focus on the actual relational concepts and SQL, rather than worry about implementation-specific minutiae. Maybe try this
tutorial or go through the Python docs