Error when move data from database to another

Question

Error on ETL code on python

I managed to learn some lines of code on python to perform ETL processes in MS SQL environment. the initial script was for PostgreSQL environment. I want to use mine for MS SQL. I tried editing the code however I got an error. Please kindly have a look

import petl as etl, pyodbc as py, sys
from sqlalchemy import *

reload(sys)
sys.setdefaultencoding('utf8')

dbCnxns = {'sample' :"dbname=sample user=user host=127.0.0.1" 
           , 'python': "dbname=python user=user host=127.0.0.1" }

#set my connection
sourceConn = py.connect(dbCnxns['sample'])
targetConn = py.connect(dbCnxns['python'])
sourceCursor = sourceConn.cursor()
targetCursor = targetConn.cursor()

sourceCursor.execute = ('SELECT name from sys.tables')


sourceTables = sourceCursor.fetchall()

for t in sourceTables:
    targetCursor.execute("drop table if exist %s" % (t[0]))
    sourceDs = etl.fromdb(sourceConn, "select * from %s" % (t[0]))
    etl.todb(sourceDs,targetConn,t[0], create=True, sample=1000)

Thank you

After some edits. I was able to write a code for MSSQL D. Here is the codes before

import petl as etl, pyodbc as py
#from sqlalchemy import *

#reload(sys)
#sys.setdefaultencoding('utf8')

#dbCnxns = {'sample' : "Driver={SQL Server} Server=USER-PC Database=sample Trusted_Connection=yes" 
#           , 'python': "Driver={SQL Server} Server=USER-PC Database=python Trusted_Connection=yes" }

#set my connection
#sourceConn = pg.connect(dbCnxns['sample'])
#targetConn = pg.connect(dbCnxns['python'])
#sourceCursor = sourceConn.cursor()
#targetCursor = targetConn.cursor()

#sourceCursor.execute = (***SELECT * FROM sample.dbo.Customer***)


sourceConn = py.connect('Driver={SQL Server};'
                      'Server=USER-PC;'
                      'Database=sample;'
                      'Trusted_Connection=yes;')

targetConn = py.connect('Driver={SQL Server};'
                      'Server=USER-PC;'
                      'Database=python;'
                      'Trusted_Connection=yes;')

sourceCursor = sourceConn.cursor()
targetCursor = targetConn.cursor()

sourceCursor.execute('SELECT name from sys.tables')

sourceTables = sourceCursor.fetchall()

for t in sourceTables:
    targetCursor.execute("drop table if exist %s" % (t[0]))
    sourceDs = etl.fromdb(sourceConn, "select * from %s" % (t[0]))
    etl.todb(sourceDs,targetConn,t[0], create=True, sample=1000)

Right now, I looks good however I am getting a programming error

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'if'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'Customer'. (4145)

Visit https://www.dofactory.com/sql/sample-database

To see the database structures I am working on.

Thank you again

Since we don't have your database, the problem cannot be easily reproduced - at the very least, can you provide the trace of the error that's being generated? — Grismar, May 14 '19 at 03:00
Error log runfile('C:/Users/user/.spyder-py3/etl.py', wdir='C:/Users/user/.spyder-py3') Traceback (most recent call last): File "C:\Users\user\Anaconda3\lib\site-packages\IPython\core\interactiveshell.py", line 3296, in run_code exec(code_obj, self.user_global_ns, self.user_ns) File "", line 1, in runfile('C:/Users/user/.spyder-py3/etl.py', wdir='C:/Users/user/.spyder-py3') File "C:/Users/user/.spyder-py3/etl.py", line 23 sourceCursor.execute = (**select name ^ SyntaxError: invalid syntax — AKP, May 14 '19 at 03:17
Replace line 23 with `sourceCursor.execute('SELECT name from sys.tables')` syntax — bkyada, May 14 '19 at 04:05
What does `targetCursor.execute("SELECT @@VERSION").fetchval()` return? — Gord Thompson, May 14 '19 at 15:15
drop table if it exist on the destination db... any thoughts @Grismar — AKP, May 14 '19 at 18:59

score 1 · Accepted Answer · answered May 15 '19 at 14:22

1

Support for DROP TABLE IF EXISTS ... was introduced in Microsoft SQL Server 2016. You are apparently using an earlier version of SQL Server, so you will have to use a workaround. See this question for details.

answered May 15 '19 at 14:22

Gord Thompson

116,920
32
215
418

You are right. I am using SQL server 2014. I will update my server and re-run the script. Thank you – AKP May 16 '19 at 15:06

Error when move data from database to another

1 Answers1