databases

A forum for general discussion of the Python programming language.

databases

Postby metulburr » Fri Mar 22, 2013 4:41 am

Ok so i have gotten accustomed to using pickle, shelve, json, for storage (in python). I have used MySql for the database of a forum, but i did nothing more than copy and paste someone's instructions and linked it with the forum, and was done with it, never to use it again.

I have never needed any database's like MySql or sqlite3, etc. I just used the small ones, like pickle, shelve, etc. 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.

So obviously i have never used these databases well. Like I am looking at the strings to send to sqlite3 from python odd, like 'SELECT' etc. And i am like "what?"

So long story short...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? Maybe i just have never worked with a large enough database though? Which brings me to my next question. 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? Are there other database's out there that work well for beginner database "users"?

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?

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.

Should i even bother learning these databases like sqlite3 or just go to mysql from the start?
New Users, Read This
OS Ubuntu 14.04, Arch Linux, Gentoo, Windows 7/8
https://github.com/metulburr
steam
User avatar
metulburr
 
Posts: 1382
Joined: Thu Feb 07, 2013 4:47 pm
Location: Elmira, NY

Re: databases

Postby Yoriz » Fri Mar 22, 2013 6:44 am

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.
Code: Select all
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

or
Code: Select all
engine = create_engine('sqlite:////absolute/path/to/foo.db')

Your tables are represented like this
Code: Select all
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)


If you need to add another column to a table at a later date this can be done.
Have a read through the tutorial
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 776
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: databases

Postby micseydel » Fri Mar 22, 2013 7:45 am

SQLAlchemy is great, but MongoDB fulfills the ability for a record to be of variable size and all.
Join the #python-forum IRC channel on irc.freenode.net!

Please do not PM members regarding questions which are meant to be discussed publicly. The point of the forum is so that others can benefit from it. We don't want to help you over PMs or emails.
User avatar
micseydel
 
Posts: 1220
Joined: Tue Feb 12, 2013 2:18 am
Location: Mountain View, CA

Re: databases

Postby setrofim » Fri Mar 22, 2013 8:12 am

Yoriz wrote: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.

Since metulburr 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:


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 that micseydel mentioned.

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
setrofim
 
Posts: 288
Joined: Mon Mar 04, 2013 7:52 pm

Re: databases

Postby jogl8 » Fri Mar 22, 2013 4:35 pm

I'm an old slow learner and I started in using sqlite3 last fall. It was no problem.

I haven't set it up on a proper server yet but that is more of a GNU Linux / networking learning curve thing.
jogl8
 
Posts: 31
Joined: Fri Feb 08, 2013 4:46 pm
Location: SW Ontario, Canada

Re: databases

Postby ichabod801 » Fri Mar 22, 2013 5:12 pm

I would agree with setrofim, use sqlite3 or something else that embeds SQL into a language you already know. SQL is a huge part of my job, and I learned it first in SAS. But that's the beauty of it: once I knew it in SAS all that knowledge transfered over to Python. I just needed to learn how Python interacted with SQL. In a couple weeks I'm giving a presentation on regular expressions to a lot of SAS users used to SQL. I think the two are a lot a like: regular expressesion are a good way to search strings and SQL is a good way to search data. And both of them are so useful they've been ported into tons of different programming languages. They've become almost like recursion or OOP: a programming tool that you can use in a wide variety of languages.

Don't be too put off by the aparent rigidity of SQL tables. There are ways to work around some of that. I work in product safety. So we have injury reports involving products. But there can be mutliple products associated with a single report, and we don't know how many products there will be for each report. One of the concepts in SQL is a one-to-many relationship between tables. So we can have a single report in one table linked with an arbitrary number of products in another table. And we can use that link backwards to find all of the reports involving a given type of product.
Craig "Ichabod" O'Brien
Minimalist, buddhist, theist, and programmer
Current languages: Python, SAS, and C++
Previous serious languages: R, Java, VBA, Lisp, HyperTalk, BASIC
ichabod801
 
Posts: 84
Joined: Sat Feb 09, 2013 12:54 pm
Location: Outside Washington DC

Re: databases

Postby metulburr » Sat Mar 23, 2013 3:30 am

wow thanks setrofim. Good Post.
New Users, Read This
OS Ubuntu 14.04, Arch Linux, Gentoo, Windows 7/8
https://github.com/metulburr
steam
User avatar
metulburr
 
Posts: 1382
Joined: Thu Feb 07, 2013 4:47 pm
Location: Elmira, NY

Re: databases

Postby jkbbwr » Sat Mar 23, 2013 11:49 am

Side note, Pickle and Shelve are the same thing just different abstractions.

I use lots of databases and to be honest, its good for a developer to learn and understand different databases its better for the programmer to make a program NON centralized to a database. For example the team I am working in a the moment are designing an attendance system for several universities, the universities all run nice big pre-existing databases. I convinced the team to prototype using MonogDB as its far faster than designing ridgid schema access and setting up and defining tables. But so that we can easily integrate the program later, we don't expose the database to the program rather we expose some methods from an interface, e.g.
Code: Select all
getStudent(string id);
updateStudent(Student student);

This means that if/when we want to deploy the software we go to the resident database specialist, we sit down and write another ~15 lines of code and boom. One fully integrated relational database.
jkbbwr
 
Posts: 17
Joined: Mon Feb 11, 2013 10:25 am


Return to General Discussions

Who is online

Users browsing this forum: No registered users and 1 guest