SQLA can execute your query as text. For an existing query like this, I'd
stick with that unless you require DB-neutral behavior or other
malleability. The constructs used would be the standard Table objects,
join objects, etc. all described in the SQL Expression Tutorial.
Marcin Krol wrote:
>
> Hello everyone?
>
> I need to translate following complex query into SQLA. How do I do that?
>
> SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.hostname AS hosts_hostname,
> h.location AS hosts_location, h.architecture_id AS
> hosts_architecture_id, h.os_kind_id AS hosts_os_kind_id, h.os_version_id
> AS hosts_os_version_id, h.additional_info AS hosts_additional_info,
> h.column_12 AS hosts_column_12, h.column_13 AS hosts_column_13,
> h.username AS hosts_username, h.password AS hosts_password, h.alias AS
> hosts_alias, h.virtualization_id AS hosts_virtualization_id, h.shareable
> AS hosts_shareable, h.shareable_between_projects AS
> hosts_shareable_between_projects, h.notes AS hosts_notes, h.cpu AS
> hosts_cpu, h.ram AS hosts_ram, h.column_24 AS hosts_column_24, h.batch
> AS hosts_batch, h.asset AS hosts_asset, h.owner AS hosts_owner,
> h.ssh_key_present AS hosts_ssh_key_present, h.machine_type_model AS
> hosts_machine_type_model, h.mac_address_eth_0 AS
> hosts_mac_address_eth_0, h.physical_box AS hosts_physical_box,
> h.up_n_running AS hosts_up_n_running, h.available AS hosts_available,
> h.project_id AS hosts_project_id, architecture.id AS architecture_id,
> architecture.architecture AS architecture_architecture, os_kind.id AS
> os_kind_id, os_kind.os_kind AS os_kind_os_kind, os_version.id AS
> os_version_id, os_version.os_version AS os_version_os_version,
> virtualization.id AS virtualization_id, virtualization.virtualization AS
> virtualization_virtualization, virtualization.color AS
> virtualization_color, project.id AS project_id, project.project AS
> project_project, email.id AS email_id, email.email AS email_email,
> rh.reservation_id AS reservation_id, r.start_date AS
> reservation_start_date, r.end_date AS reservation_end_date, r.status AS
> reservation_status, r.businessneed AS reservation_businessneed,
> r.notetohwrep AS reservation_notetohwrep, r.email_id AS
> reservation_email_id, r.project_id AS reservation_project_id
>
> FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON h.id = rh.host_id
> LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id
> INNER JOIN
>
> (SELECT h.id, MIN(r.start_date) FROM hosts h
> LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id
> LEFT OUTER JOIN reservation r ON rh.reservation_id = r.id GROUP BY h.id
> ORDER BY h.id) AS min_date(m_host_id, m_start_date)
>
> ON h.id = min_date.m_host_id AND (r.start_date = min_date.m_start_date
> AND (r.start_date,r.end_date) OVERLAPS ('2009-04-10'::date,
> '2010-04-10'::date) OR min_date.m_start_date IS NULL)
>
> INNER JOIN architecture ON architecture.id = h.architecture_id
> INNER JOIN os_kind ON h.os_kind_id = os_kind.id
> INNER JOIN os_version ON h.os_version_id = os_version.id
> INNER JOIN virtualization ON h.virtualization_id = virtualization.id
> INNER JOIN project ON h.project_id = project.id AND project.project =
> 'LMT'
> LEFT OUTER JOIN email ON r.email_id = email.id
>
> WHERE h.id NOT IN (
> SELECT m.host_id
> FROM reservation r, reservation_hosts m
> WHERE r.id = m.reservation_id
> AND m.host_id IS NOT NULL
> AND (r.start_date <= '2009-04-10'::date AND r.end_date >
> '2009-04-11'::date)
> ORDER BY h.id, r.start_date)
>
>
> AND h.up_n_running = True AND h.shareable = True ORDER BY virtualization
> DESC, ip ASC
>
> Regards,
> mk
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
