I used the below python code to export the table data from my local server to the required folder in DB2
import os
import ibm_db_dbi
conn = ibm_db_dbi.connect('sandy','USER','qwerty')
cursor = conn.cursor()
table_name_list = ['employee','staff','department'] #contains list of table name
for table_name in table_name_list:
print(table_name)
path = os.path.join(r"C:\DB2\UPLOAD_DATA", table_name)
print(path)
SQL = "export to " + path + "\\" + table_name +".csv of del select * from "+ table_name
print(SQL)
cursor.execute(SQL)
But when I tried to execute the above code I am getting an error,
employee
C:\DB2\UPLOAD_DATA\employee
export to C:\DB2\UPLOAD_DATA\employee\employee.csv of del select * from employee
ibm_db_dbi.ProgrammingError: ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2/NT64] SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement. Invalid character: "\\". Text preceding the invalid character: "export to C:". SQLSTATE=42601\r SQLCODE=-7
Someone, please help me to solve this error
You get this error because your code is incorrect, and you could have found this if you searched more.
The EXPORT is a command, it is not SQL, and consequently you cannot use the execute() method because that expects only SQL. Normally you use such commands in shell scripts, or in CLP scripts for the Db2 command processor which lets you mix both SQL statements and commands in one script - but not python.
To workaround this, you can instead call a stored procedure (only if your Db2-server platform is Linux/Unix/Windows). You cannot use this on a shared cloud instance of Db2.
To call a stored procedure, you can use the python callproc() method, and the name of the stored procedure is sysproc.admin_cmd , and you give this procedure a command line which is the EXPORT command. The documentation is here.
Your account needs suitable authorisation to be able to run this stored procedure, and the most important thing to understand is the the filename (for what you export) can only be relative to the file systems mounted on the Db2-server , in other words you cannot write to a client side file system. So if your client and Db2-server are on different hostnames, you must use another technique to access the exported data file from the client (again possible by suitable mounting, or by copying the file etc).
If your Db2-server runs Linux/Unix/Windows and is a rececent version then it will also let you insert into an external table , which can be a flat file located anywhere addressable from the Db2-server. This can be an alternative to export for certain use cases.