Maby you could add pipe sections to the screw pump and make it pump from deeper.
Pumping (IRL, dwarven physics notwithstanding) doesn't work like that. You cannot "suck" water up a significantly long pipe, as you just end up with a vacuum (or nearly so) at the top, which the nominal pressure of the water at the bottom won't cause to be filled by upward-flowing liquid.
You have to pump up from the bottom end. Either by putting the pump there (hopefully strong enough to be able to feed its liquid up rather than burst itself) or by sending
down liquid (to create the lower-end pressurisation needed to convey the other liquid up). Alternatively, a set of "sucking" pumps at intervals, capable of lifting at least the distance between themselves and the next pump below (or the bottom entry, for the lowest) in a chain.
For clearing aquifer-flooding drill-shafts (for now excluding the problems of magma, with its denser liquid and heat-tolerance requirements) while
not refilling it with another liquid (like water is used to pumped into oil reservoirs and retrieve the latter's load) would perhaps need a pump-like component for every 'n' drill-axles attached to the top and then lowered further. Or just wait until you've punctured a cavern/magma-sea and let it drain and/or undergo obsidianised cave-ins until empty.
(Not sure if it's at all in-period. And I also don't know why people consider tunnelling dwarves to be suicide-workers. I often dig 1x1 vertical well-shafts down to the caverns (up to and beyond 40Z-levels, sometimes), without losing the dwarf at all, or even the materials that he digs through. I've given my methods elsewhere many times, so not sure you want to hear them again. Obviously if I hit an aquifer, then I don't actually punch through it, because in this case it's water I'm after in the first place.)