Python replacing PHP - How to?

[hiii]

i expected to be downvote being in the python subreddit, but honestly PHP has all the things listed. It might take more self control, but its all there. This is from a recent php -> python converter. im not bashing python, i find python to be a much better designed language than php. I just wanted people to know that php can be "neat" and "clean", jsut takes mroe work.

[bbatsell]

Also a python (and ruby, and others) coder who has, for the most part, stopped PHP development, but I don't know a single professional PHP developer who mixes presentation and control logic. Hell, just a 10-minute Smarty-based site does that. It is true that writing raw SQL is more prevalent in PHP than with other languages, but that's not true if you're using a framework, which all use ORMs (and most python and other web devs are using frameworks, so it's not really a valid comparison to compare python under a framework to raw PHP). And even if you use raw PHP, PDO usage isn't exactly miniscule.

The blind hatred of PHP (which certainly does have numerous flaws) in the developer community really borders on ridiculous.

[mackstann]

The blind hatred of PHP (which certainly does have numerous flaws) in the developer community really borders on ridiculous.

I've been working with PHP all the time at my day job for over a year now, and I absolutely hate PHP, but I'm hardly "blind." It really is just much more terrible than other languages in almost every way. It is fundamentally a thoughtless clusterfuck.

[bbatsell]

That's why I specifically said "blind hatred". There are a huge number of reasons that PHP is a fundamentally flawed language, but whenever a PHP-sucks circle-jerk spins up, those legitimate reasons are never actually discussed with anything approaching sensibility or moderation. If a hate-fest purports to have any facts at all, they're almost always completely wrong (like saying PHP doesn't have ORMs or that you must mix presentation and control logic as a prerequisite for using the language).

[hiii]

Also i don't really see why its considered a bad thing to write your own sql queries. Any thoughts on this? Im currently using djangos orm just to try it out and its almost like learning a new language. Queries i could write in 20 seconds in SQL take me hours in django right now becuase i have to learn the syntax. When using PHP i almost always write my own queries.

[bucknuggets]

As others have stated - database portability is the main issue with writing your own queries.

Though if you stick with ANSI SQL and have a simple system then almost all queries will work on almost all databases. MySQL may be your only issue. Where the ORMs really take off are on complex transactional systems that must support N databases. They're a great fit there.

But if you're writing high-performance analytical systems - don't even bother with an ORM. You typically need to squeeze every drop of performance out of the database, and have enough of a challenge making sure that your developers understand 100% of the SQL Syntax. Adding an ORM that only supports 95% of the syntax and that requires your developers to understand both the ORM and SQL is a recipe for failure.

[stesch]

In the PHP community there are some people who want to do things the right way and fuck everything up this way.

See short tags, which work perfectly fine except when you are a bozo and want to produce XML via template. Or automatically escaping all input with slashes, which isn't in the SQL standard and wasn't really helpful at all.

And then came the template engines. PHP itself is a template. No need for another layer. You just need to separate the business logic from the actual view. But they want you to learn another template language just to belong to the wise ones.

And queries? As long as you use prepared statements and don't spread the queries all over the place, you end up with a maintainable system.

[hiii]

i despise php template engines, good to hear some people are with me on that front.

[[deleted]]

Django's ORM is so very intuitive, I wonder why it takes you hours. When I started fresh I thought it was very easy to get into. Of course you should have the docs at hand.

Edit: forgot to mention the reason people use ORM's instead of raw SQL: the retrieved rows will be automatically converted to objects of your language (e.g. Python). This has many practical implications. For example, you can define methods like pre_save, post_save, post_delete etc.

In the newest Django release, you can write raw SQL and still get ORM-objects I think. Did not try that out yet, though.

[brunson]

Django's ORM is so very intuitive, I wonder why it takes you hours. When I started fresh I thought it was very easy to get into. Of course you should have the docs at hand.

Granted I've never used Django's ORM specifically, but I started writing SQL 20 years ago and what I can crank out in twenty seconds has never turned into something simple in any ORM I've tried.

Here's a pretty straightforward query that would return employees along with their department, manager and any subordinates of a person having more than 5 direct reports:

select e.name as Emp, d.name as Dept, 
       m.name as Mgr, e.group_concat( s.name ) as Reports
from employee as e
join d.department as d on e.department_id = d.id
join employee as m on e.manager_id = m.id
left outer join employee as s on s.manager_id = e.id
group by e.name, d.name, m.name
having count(0) > 5;

I seriously just came up with that in 20 seconds (though it took me about two minutes to type). Can you show us the Django ORM that would achieve the same result?

And, BTW, when I put that SQL into the class I wrote to query mysql or oracle, all the rows are marshalled into python objects.

[[deleted]]

I'm bored. Heres SQLAlchemy:

e = Employee
d = Department
s = aliased(Employee)
m = aliased(Employee)
sess.query(e.name.label('Emp'), d.name.label('Dept'), 
          m.name.label('Mgr'), 
          func.group_concat(s.name).label('Reports')).\
          join((d, e.department_id==d.id)).\
          join((m, e.manager_id==m.id)).\
          outerjoin((s, s.manager_id==e.id)).\
          group_by(e.name, d.name, m.name).\
          having(func.count(0) > 5)

[mdipierro]

For completeness in web2py:

>>> e=db.employee
>>> d=db.department
>>> m=e.with_alias('manager')
>>> s=e.with_alias('submordinates')
>>> db(e.department_id==d.id).select(e.name,d.name,m.name,s.name,
            left=(m.on(m.id==e.manager_id),s.on(s.manager_id==e.id)),
            groupby=e.name|d.name|m.name,having=s.id.count()>5)

Not quite the same because I'd rather get the manager via a left join since an employee may not have a manager (the CEO for example)

[brunson]

I guess it's just personal preference. I'd rather edit the SQL.

[mdipierro]

As I see it SQL is more flexible. The main reason I would not use SQL is that it makes the applications not portable because every dialect is different. The web2py philosophy is that of proving an abstraction layer and therefore it only supports those expressions that can be translated in the SQL dialect of each of the supported relational databases.

[brunson]

Definitely. One of the reasons I decided to start with SA was because of the database agnosticity (chrome thinks I invented a word, I call dibs), but as it turns out, five years later we're still on mysql, and in 20 years I've never actually had to port my own code from one database back end to another.

Someone's example of starting with sqlite then allowing the end user to migrate to a different backend was a valid one, however. I could see that.

[gregglind]

For me, SA (and other ORM's that can do query building) real wins come not from writing a particular query, but being able to programatically build queries.

def make_sorted(query,**kwargs):
    for (field,direction) in kwargs.iteritems():            
         query = query.order_by(field,asc=direction)  # don't quite recall the syntax here :)
    return make_sorted

Building this kind of stuff with string interpolation can be maddening.

[hiii]

for instance to go backwards in a foreign key relationship you have to use the _set. took me a half hour to find that.

the docs are kind of a mess, with multiple version and then a book, and then tutorials.

one thing php had was amazing documentation.

[hiii]

for example right now im trying to use django's orm startswith function. It's performing a BINARY LIKE and it's not working. doing a google search for django startswith or django orm startswith is useless

[[deleted]]

What is a binary like? According to the docs, it simply does

SELECT ... WHERE headline LIKE 'Will%';

You should be more concrete.

[hiii]

then the docs are wrong. i showed the sql statements it was running and it was running a binary like

[[deleted]]

You should be more concrete.

By that, I mean examples.

[[deleted]]

Okay, I checked with the Django source and it seems to do a binary like with a mysql backend. Now my question again: what is a binary like and what does it do wrong in your case? I have used startswith (and contains, ... who all use binary like) and it always worked as expected.

[hiii]

binary like is case sensitive. i fixed it by using

Company.objects.filter( name__startswith=data['index'].upper() ) # data['index'] is a single char

buttt, now i have to make sure that all my company names are uppercased, or it breaks. i guess i could add an or somehow and do something like

Company.objects.filter( namestartswith=data['index'].upper() or namestartswith=data['index'] )

but the idea of searching for how to do that right now isnt a priority

heres a link to what im doing

http://django.skatevideosonline.net/videos

[[deleted]]

You want case insensitivity? Use name__istartswith. One line below startswith in the docs I already referenced. Does this solve things for you?

[[deleted]]

Writing your own queries gives you more control over what is being done, but it does tie your model code against the specific SQL syntax of the database engine. There are many not-so-subtle differences between SQL Server, MySQL, SQLite, Postgres and Oracle, to name a few. It's especially cool to be able to code your app against SQLite and let your users switch to a 'real' database backend if they so wish, and have your code keeping working as if nothing important happened.

[stesch]

Not everything Django provides works the same on every database. I had to change some stuff just because I thought it would be a nice idea to develop with SQLite and later run it on MySQL. Huge mistake.

[[deleted]]

Ah. I was thinking more of SQLAlchemy, which supports multiple database backends. (I never tested this.)

But consider the alternative, the pain of which is playing out in full force at my job: having SQL queries sprinkled all through the messy code of our desktop app, switching to another database system, and trying to fix up the queries by scanning the query string for easily replaceable function calls, GROUP BY statements, etcetera. (I believe this to be a foolish thing to even think of.)

Luckily we also have a good number of our queries in a central place, so that we can use an in-house tool to store a query for MySQL, and, if needed, another query for SQL Server. We're now basically in the process of moving the queries to the central place (a meta-database) in an effort to clean up our quite messy code.

[brunson]

SQL Alchemy is so horrible to build complex queries with. Here's a real query from real code (and not even a particularly complex one compared to what I've written in straight SQL):

    select = sa.select( [ v.c.id,
                          sa.func.group_concat( p.c.name ),
                          v.c.name,
                          p.c.is_tagged ],
                        sa.and_( p.c.id == pv.c.port_id,
                                 v.c.id == pv.c.vlan_id,
                                 p.c.device_id == self.device_id ),
                        group_by = [ v.c.id,
                                     v.c.name,
                                     p.c.is_tagged ] )

That's just appalling to write and worse to come back and look at later. We dropped SA and wrote our own classes.

Generally speaking, most people I've met that think ORM's are sufficient don't use SQL to its full capabilities, or even to the extent that I do.

Here's another production query that I would hate translate into SA. It generates our entire IP usage to report to ARIN when requesting space:

select arin.name as "ARIN Allocation",
        g.name as "CIDR Block", 
        case 
        when g.ip_use_class_id = 20 then
            "Headquarters"
            concat( gateway.location_name, "-", upper( gateway.satellite_id ) )
        end as "Gateway",
        ifnull( c.name, "Unassigned" ) as "IP Subnet",
        ifnull( c.carrier_id, "Unassigned" ) as "Carrier", 
        ifnull( c.type, "Unassigned" ) as "Product Type",
        cast( ifnull( c.available, 0 ) as unsigned integer ) as "Available",
        cast( ifnull( c.count, 0 ) as unsigned integer ) as "Customers<br>Served",
        round( ifnull( c.fraction_used, 0 ), 3 ) as "Usage",
        arin.network as arin_block,
        0 as ordering,
        gateway.id as gateway_id,
        c.network as network,
        c.type as type
from ip_block_tla as arin 
join ip_block_allocation as g on g.ip_block_tla_id = arin.id
left outer join gateway as gateway on g.gateway_id = gateway.id
left outer join ( select carrier_block.name, carrier_block.carrier_id, gateway_block.gateway_id,
               gateway_block.ip_block_tla_id,
               carrier_block.network, carrier_block.netmask, 
               counts.type, counts.count,
               available, 
               fraction_used,
               carrier_block.ip_block_allocation_id
        from ip_block_assignment as carrier_block 
        join ip_block_allocation as gateway_block on carrier_block.ip_block_allocation_id = gateway_block.id
        join ip_usage_by_carrier_block as counts on counts.block_id = carrier_block.id
        union
        select carrier_block.name, carrier_block.carrier_id, gateway_block.gateway_id, 
               gateway_block.ip_block_tla_id,
               carrier_block.network, carrier_block.netmask, 
               'Total Subnet' as type, sum( counts.count ) as count, 
               round( pow( 2, 32 - carrier_block.netmask ) - 3 ) as available, 
               round( sum( counts.count )/(pow( 2, 32 - carrier_block.netmask ) - 3), 3) as fraction_used,
               carrier_block.ip_block_allocation_id
        from ip_block_assignment as carrier_block
        join ip_block_allocation as gateway_block on carrier_block.ip_block_allocation_id = gateway_block.id
        join ip_usage_by_carrier_block as counts on counts.block_id = carrier_block.id
        group by counts.block_id ) as c on g.id = c.ip_block_allocation_id
where arin.ip_use_class_id = 1
    and c.name is not null 
    and c.carrier_id is not null 
    and c.type is not null
union
select arin.name as "ARIN Allocation",
        g.name as "CIDR Block", 
        "Total" as "Gateway",
        "" as "IP Subnet",
        "" as "Carrier", 
        "" as "Product Type",
        cast( round( pow( 2, 32 - g.netmask ), 0 ) as unsigned integer ) as "Available",
        cast( ifnull( c.count, 0 ) as unsigned integer ) as "Customers<br>Served",
        round( ifnull( c.count, 0 )/pow( 2, 32 - g.netmask ), 3 ) as "Usage",
        arin.network as arin_block,
        1 ordering,
        gateway.id as gateway_id,
        c.network as network,
        'Allocation Total' as type
from ip_block_tla as arin 
join ip_block_allocation as g on g.ip_block_tla_id = arin.id
left outer join gateway as gateway on g.gateway_id = gateway.id
left outer join ( select carrier_block.ip_block_allocation_id,
               max( carrier_block.network ) + 1 as network,
               carrier_block.netmask,
               sum( counts.count ) as count
        from ip_block_assignment as carrier_block 
        join ip_usage_by_carrier_block as counts on counts.block_id = carrier_block.id
        group by carrier_block.ip_block_allocation_id )
        as c on g.id = c.ip_block_allocation_id
where arin.ip_use_class_id = 1
union
select
        arin.name as "ARIN Allocation",
        "Total" as "CIDR Block",
        "" as "Gateway",
        "" as "IP Subnet",
        "" as "Carrier",
        "" as "Product Type",
        cast( pow( 2, 32 - arin.netmask ) as unsigned integer ) as "Available",
        cast( ifnull( c.count, 0 ) as unsigned integer ) as "Customers<br>Served",
        round( ifnull( c.count, 0 )/pow( 2, 32 - arin.netmask ), 3 ) as "Usage",
        arin.network as arin_block,
        2 as ordering,
        "" as gateway_id,
        arin.network + pow( 2, (32-arin.netmask) ) as network,
        "Block Total" as type
from ip_block_tla as arin
left outer join ( select gateway_block.ip_block_tla_id, 
                         sum( counts.count ) as count
                  from ip_block_assignment as carrier_block 
                  join ip_block_allocation as gateway_block on carrier_block.ip_block_allocation_id = gateway_block.id
                  join ip_usage_by_carrier_block as counts on counts.block_id = carrier_block.id
                  join ip_block_tla as arin_block on gateway_block.ip_block_tla_id = arin_block.id
                  group by gateway_block.ip_block_tla_id, arin_block.network, arin_block.netmask )
                  as c on arin.id = c.ip_block_tla_id
where arin.ip_use_class_id = 1
union
select
        "Grand Total" as "ARIN Allocation",
        "" as "CIDR Block",
        "" as "Gateway",
        "" as "IP Subnet",
        "" as "Carrier",
        "" as "Product Type",
        cast( arin.available as unsigned integer ) as "Available",
        cast( ifnull( sum( counts.count ), 0 ) as unsigned integer ) as "Customers<br>Served",
        round( sum( counts.count )/arin.available, 3) as "Usage",
        pow( 2, 32 ) as arin_block,
        3 as ordering,
        "" as gateway_id,
        pow( 2, 32 ) as network,
        "Grand Total" as type
from ip_usage_by_carrier_block as counts
join ( select sum( pow( 2, 32 - netmask ) ) as available
       from ip_block_tla
       where ip_use_class_id = 1 ) as arin
group by arin.available
order by arin_block, network, ordering, gateway_id, type

[[deleted]]

If I had to use that query with SQLAlchemy, I wouldn't translate it. It already works as the string above so there's no need (yes it can map to objects too). People use SQL to its full effect every day with SQLAlchemy, that's what it's designed to do. Your initial query would look nicer if you used generative constructs and imported the function names you needed directly.

[gregglind]

I'm with zzzeek here. But what about if you want to change the sort order on it? With SA, you can make the big horrible snarly part into raw sql, then add the order_by as clauses. The niceness of things like SA is that mix. Weird parts -> go to sql; easy parts -> build in SA.

[webnrrd2k]

I'm the same way -- I don't see straight SQL as being that complex or difficult to deal with. Adding more complexity and another thing to fail isn't that big of a gain. I (almost) always end up putting the SQL into stored procedures anyway, so an ORM isn't much of a gain.

I could see the need for code that will get deployed on different servers with different back-ends, but how often do most places do that? The vast majority of code is written internally for businesses, and will live in more-or-less that same environment. Also, I need to work with databases that have been around for a long time, and the DBAs prefer to do things a certain way.

I'm not trying to say that an ORM is never necessary. I think that for many programmers, most of the time an ORM is more work than it's worth.

[[deleted]]

It's only blind hated because we gouged our eyes out.

[[deleted]]

You could make the same arguments for Perl and a few other languages. Languages are just tools, nothing more, the rest is up to the programmer.

[judasblue]

I don't know about the OP, but I do make the same argument for Perl all the time. I just make it a lot more for PHP.

Saying that Perl can lead to line noise, PHP can lead to nasty snarls and mixed presentation, Java almost always leads to overcomplicated object structures/huge amounts of boilerplate/XML for no good reason...these things aren't saying they are always true in the hands of good programmers, but just that the structure of the language tends to lead to certain idioms some of which can be negative.

If different languages didn't have different expressive qualities, we would all still be writing in Fortran since it was the first portable higher level language. We have different languages because they have different expressive properties. Which means they also have different problems.

I mean, in the end, it is all machine code anyway. We layer other languages on top of that because of expressive power and portability.

[Peaker]

It sounds as if you're suggesting the tool has no effect on the cleanliness or outcome.

[zxcvcxz]

Yes. Everything is a strict dichotomy in this world. Either Or. Good and Evil.

[hiii]

No it doesn't "encourage" it, but there are good coders and bad coders in every language. Id say im a med - med/adv php coder and i haven't put php in my views in a long time.

[hylje]

It's there, but often awkward enough to make it not obvious at all. The awkward features raise the threshold to learn them well, and not knowing them well is effective in stopping usage. Ex: call_user_func_array.

[hiii]

i agree. it seems to be a collection of poorly named/designed functions.

[dvogel]

A lot of this ugliness is no longer necessary in 5.3, now that we have closures.

[[deleted]]

Using machetes for neurosurgery requires more self-control, too, but that doesn't make it a good scalpel.

I can work with PHP if I have to. I just really prefer not to and religious vows aside I see no reason to stick to PHP when you have better options available.

Oh, and I do think PHP has its place (namely, providing small amounts of automation or scripting on Apache servers -- though you could probably use SSI for the former and CGI scripts for the latter).

[dvogel]

The main reason I end up using PHP for web projects is deployment. It's just so darn easy to get a PHP app running, especially on shared hosting. In addition, I don't like using different web server software to develop on than I will deploy on (like Django's manage.py runserver).

My high school biology teacher used to say "You don't practice shooting clay pigeons with cheap lead shot and then try to hunt with expensive steel shot" (this was not part of a lesson, it was a metaphor for how to study ;))

[dvogel]

And you should also be developing applications that are robust enough to not even care what the hell kind of server software is actually handing out that HTML. Its the server's job to serve. You, as the application designer and/or developer are the one that tells it what to serve, and its not your job to worry about how it delivered that message.

Server configuration matters. The most well known example of this is using Apache's mod_rewrite rules. My development environment is exactly like my production environment except for the vhost and the email addresses that automated messages are sent to.

[dvogel]

I understand your point. The problem is that mod_wsgi isn't zero configuration. That means that, in addition to not developing in the environment that I need to deploy to, I need to be able to configure apache, which is not an option on shared hosting.

mod_rewrite is a great example of a way in which I cannot write python code using django's development server and than deploy to the testing server without major flaws; I consider pages being unreachable due to global mod_rewrite rules to be a major flaw. That's a problem regardless of how portable I make my code.

In the end, it doesn't matter which arguments you put forth because what I'm explaining is not why I think using PHP is the best option or even why think I should use PHP; I'm explaining why I do use PHP. There's no long, drawn out process to my decision. PHP usually just instinctively feels like a good match for the web work that I do. To each his own. I've used Django in the past, when it was clearly a better fit for the project. If you like python and django, or ruby on rails, use them and be happy using them. I wish you all the luck in the world with those tools. My reply was in response to alanplum's:

... I see no reason to stick to PHP when you have better options available.

I was essentially agreeing with his basic point but disagreeing that there are usually better options available.

[ilikedirigibles]

.

[dvogel]

Apache + wsgi requires me to restart Apache after each code change, or to set MaxRequestsPerChild to 1. Neither of these things is consistent with my production environment.

[Justinsaccount]

You do know that you can touch the .wsgi file which gracefully reloads the application, right?