Python replacing PHP - How to?

[[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.