[ale] MySQL help
David Jackson
deepbsd.ale at gmail.com
Sat Mar 14 17:42:49 EDT 2015
Thanks DJ. I had to read the wiki article on what am ORM was!
This course only has two chapters on databases, so I'm guessing we dive
deeper in another course!
Thanks again!
Dave
On Fri, Mar 13, 2015 at 4:36 PM, DJ-Pfulio <djpfulio at jdpfu.com> wrote:
> ORMs are double edged.
>
> Some are buggy and create lots of opportunities for SQL-injection attacks.
>
> Some are slow and suck RAM ... like Java, Outlook, iTunes.
>
> Some will be faster to code, create better (faster/safer) SQL than an
> intermediate developer, only call the DBMS when results are actually
> needed,
> and protect against all the currently known attacks ... I'm talking about
> DBIx,
> of course. The ORM that all other languages wish they had. ;)
>
> Oh - and more ORMs abstract the DBMS away - swap in whatever back-end DB
> you
> like - mysql, postgres, sqlite, mariadb ... are usually each supported with
> others often supported like Oracle, DB2, Informix, etc. Some ORMs might
> support
> noSQL too, but using a specialized class is probably best.
>
> I haven't written any direct SQL in webapps in about 7 yrs. It just hasn't
> been
> worth my time. If there are fewer than 20K concurrent users, I wouldn't
> bother
> writing SQL until specific profiling showed it was needed.
>
> In the 1990s, my company did "migrations" for our DBs in a way similar to
> Rails::Migrate ... Clearly, I like that method and wish more ORMs did it
> that way.
>
> For internal corporate webapps, I'd always start with an ORM regardless of
> language - perl, python, ruby. These are each wonderful languages and
> folks
> should understand how great it is to have choices like these today.
>
> Perl + DBIx + Dancer + CPAN is an awesome toolkit for whipping out a
> webapp or
> creating an enterprise web application developed for years.
>
> Ruby + Rails/Sinatra + ActiveRecord (or one of 10 other Ruby ORMs) ...
> ruby-gems
> is only slightly less so. Gems and ActiveRecord are beasts, but ruby is a
> gorgeous language. Maintaining Gems takes too much RAM. I've had to add a
> GB of
> RAM to VMs to patch Gems, otherwise, the updates would never complete.
> Then take
> the RAM away to keep monthly costs reasonable.
>
> No experience with python - my personal issue with languages that care
> about
> whitespace. Wish python were my first language instead of FORTRAN 66, so I
> wouldn't have the issue.
>
> Of course, this is just 1 person's opinion. Certainly, direct SQL written
> by an
> expert can be great too, but getting to that level of competence is just
> not
> necessary most of the time.
>
>
> On 03/13/2015 03:00 PM, Charles Shapiro wrote:
> > Heh. Hadda look that one up. Could work great, depending on resources
> and
> > purpose.
> >
> > -- CHS
> >
> >
> > On Fri, Mar 13, 2015 at 2:51 PM, DJ-Pfulio <DJPfulio at jdpfu.com> wrote:
> >
> >> Or just use an ORM.
> >>
> >> On 03/13/2015 02:36 PM, Charles Shapiro wrote:
> >>> Hmm. I'm not that familiar with MySQL, but in PostgreSQL the way to
> >> determine
> >>> what you want is
> >>>
> >>> select
> >>> animal.id <http://animal.id>
> >>> from
> >>> animal
> >>> left join
> >>> food
> >>> on
> >>> animal.id <http://animal.id> = food.animalid
> >>> where
> >>> food.id <http://food.id> is null
> >>> ;
> >>>
> >>>
> >>> Writing SQL queries takes some practice. One handy tip I've found is
> to
> >> write
> >>> the "from..." part first, then go back and fill in what columns you
> wish
> >> to select.
> >>>
> >>> Doing anything based on the numeric value of the "id" field is a Bad
> >> Idea. In
> >>> most relational databases, that field just increments when you add a
> >> record to
> >>> your table. It never decrements when you delete records. That means
> >> that the
> >>> ID value itself is useless in determining what is or is not in a table.
> >> It's
> >>> only useful when it's in another table -- hence the name, "Relational".
> >>>
> >>>
> >>> Your current table setup also breaks down if you add -- say -- a goat
> >> which
> >>> wants to eat the same things as the giraffe. You're more likely to have
> >> several
> >>> animals linked to the same foods than several foods linked to the same
> >> animal.
> >>> Don't you really want a "FoodFk" in the "animal" table? Then you could
> >> have
> >>> lots of animals which eat the same thing. Of course, I reckon in
> >> reality you'd
> >>> need a jump table to link foods and animals:
> >>>
> >>> food_animal { integer animalid references animal(id), integer foodid
> >> references
> >>> food(id) }
> >>>
> >>> Then you could have lots of different foods and lots of different
> >> animals all
> >>> linked together.
> >>>
> >>>
> >>> -- CHS
> >>>
> >>>
> >>> On Fri, Mar 13, 2015 at 1:52 PM, David S Jackson <
> deepbsd.ale at gmail.com
> >>> <mailto:deepbsd.ale at gmail.com>> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I'm a MySQL newbie. I'm just starting to use Python to talk to
> MySQL
> >>> databases, but first I need to understand the MySQL query language!
> >>>
> >>>
> >>> So, I have two tables: (zoo) animal and food.
> >>>
> >>> animal has the columns ID, NAME, FAMILY, WEIGHT
> >>>
> >>> food has ID, ANIMALID, FEED
> >>>
> >>>
> >>> So if I go: select id, name, family, feed from animal JOIN food ON
> >> animal.id
> >>> <http://animal.id>=food.animalid;
> >>>
> >>>
> >>> I get something like:
> >>>
> >>> ID NAME FAMILY FEED
> >>> 1 Seymore Snake mice, leaves
> >>> 2 Gerard Giraffe leaves, grass
> >>> ...
> >>> etc
> >>>
> >>> So, if I have a situation where I want to see whether an animal has
> >> been
> >>> added the animal table but may have not been added to the food
> >> table, how
> >>> would I compose that query?
> >>>
> >>> That is, the animal could have been added to the animal table and
> >> could get
> >>> an id, say 10, but the food table could have 20 foods already
> >> entered and
> >>> the animal-id would be used several times.
> >>>
> >>> I was thinking, is there a way I can ask, "does the highest
> >> animal.id
> >>> <http://animal.id> number equal the highest food.animalid number"?
> >> If not,
> >>> what animal is not getting fed?
>
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20150314/9ec1bf4f/attachment.html>
More information about the Ale
mailing list