0

Goal

Using asyncio, asynchronously run the bcp utility to extract multiple tables to .dat files. Commands when run independently, yield expected results. Commands also executed successfully when doing sequential execution through subprocess.run. Believe error is due to how I'm using or not using await.

Error

Traceback (most recent call last):
  File "bcp_out_all.py", line 38, in <module>
    asyncio.run(windows_loop([cmd_ for cmd_ in bcp_cmds]))
  File "C:\Users\hSin\AppData\Local\Continuum\anaconda3\envs\ELT\lib\asyncio\runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "C:\Users\hSin\AppData\Local\Continuum\anaconda3\envs\ELT\lib\asyncio\base_events.py", line 583, in run_until_complete
    return future.result()
  File "bcp_out_all.py", line 14, in windows_loop
    loop.run_until_complete(parallel_bcp(cmd_))
  File "C:\Users\hSin\AppData\Local\Continuum\anaconda3\envs\ELT\lib\asyncio\base_events.py", line 570, in run_until_complete
    self.run_forever()
  File "C:\Users\hSin\AppData\Local\Continuum\anaconda3\envs\ELT\lib\asyncio\base_events.py", line 528, in run_forever
    'Cannot run the event loop while another loop is running')
RuntimeError: Cannot run the event loop while another loop is running
sys:1: RuntimeWarning: coroutine 'parallel_bcp' was never awaited

Script

#bcp_out_all.py
import asyncio

#commands successfully run in PowerShell terminal
bcp_cmds = [['bcp db.schema.tbl_1 OUT tbl_1.dat -e tbl_1_error.dat -T -n -t"|" -S SRVRNAME'], ['bcp db.schema.tbl_2 OUT tbl_2.dat -e tbl_2_error.dat -T -n -t"|" -S SRVRNAME']]


async def parallel_bcp(cmd_):
    process = await asyncio.create_subprocess_exec(cmd_, stdout=asyncio.subprocess.PIPE, stderr=asyncio.subprocess.PIPE)
    await process.wait()

async def windows_loop(cmd_):
    loop = asyncio.ProactorEventLoop()
    asyncio.set_event_loop(loop)
    loop.run_until_complete(parallel_bcp(cmd_))

asyncio.run(windows_loop([cmd_ for cmd_ in bcp_cmds]))
hSin
  • 350
  • 2
  • 11
  • 1
    You don't need to use `run_until_complete` in an async function such as `windows_loop` - you should simply `await parallel_bcp(cmd_)`. Or, since your `cmd_` is actually a list, you should use something like `await asyncio.gather(*[parallel_bcp(c) for c in cmd_])` – user4815162342 Mar 05 '20 at 13:20

1 Answers1

0

Was able to figure out solution based off this question; How do I pass a string in to subprocess.run using stdin in Python 3 , however, its application was in regards to Python 3.7 and the command being passed as list of strings, which my command list was modified to. Lastly, subprocess was used in place of asyncio.

Updated Script:

from subprocess import Popen

procs_list = ['bcp db.schema.tbl_1 OUT tbl_1.dat -e tbl_1_error.dat -T -n -t"|" -S SRVRNAME', 'bcp db.schema.tbl_2 OUT tbl_2.dat -e tbl_2_error.dat -T -n -t"|" -S SRVRNAME']

for proc in procs_list:
   Popen(proc, text=True)

Final Script:

Ended up not needing to use asyncio, and took advantage of Popen.

tbls_lst = ['tbl1', 'tbl2']

procs_lst = ['bcp db.schema.' + tbl + ' OUT ' + tbl + '.dat' + '-T -n -t"|" -S SRVRNAME -b 5000' for tbl in tbls_lst]

try:
    for proc in procs_lst:
        process = subprocess.Popen(
                proc,
                stdout=subprocess.PIPE,
                stderr=subprocess.PIPE,
                text=True)
        output, error = process.communicate()
        if output:
            print("Output returned: ", process.returncode)
        if error:
            print("Error returned: ", process.returncode)
            print("Error:", error.strip())
except OSError as e:
    print("OSError: ", e.errno)
    print("OSError: ", e.strerror)
    print("OSError: ", e.filename)
except:
    print("Error: ", sys.exc_info()[0])
hSin
  • 350
  • 2
  • 11
  • Can you please share the whole code? I run the following (based on your example): `p=Popen(cmd, text=True)` then `process=await asyncio.create_subprocess_exec(cmd, ...)` then `await process.wait()` then `process.kill()` then `os.killpg(os.getpgid(p.id, signal.SIGTERM))` and this runs just perfect **BUT** the bcp.exe (on windows) continues to run in background, and has a huge impact on database. I had to kill all parallel jobs from `cmd`. Thus, please provide full example! Thanks! – abe Apr 30 '20 at 19:57
  • @abe I updated the script, ended up going a different route. – hSin May 01 '20 at 15:51
  • Many thanks. But does this run also asynchronous? In my version I have implemented `producer` and `consumer`. Thus, `producer` gives, e.g., 20 tables and there are, e.g., 10 `consumers`. Thus, I need this code run asynchronous. – abe May 01 '20 at 16:05
  • @abe I believe it does, been a while since I worked with the script. – hSin May 01 '20 at 17:00
  • Ok thanks! Last question, how do you terminate the process? I miss smth like `process.kill()` or so ... – abe May 01 '20 at 19:26