I am new to odo. I have a simple function for migrating databases:
from blaze import Data, odo, like
def migrate(source, target, table_names=None):
src_db = Data(source)
tbl_names = (src_db.fields, table_names)[table_names is not None]
for tbl in tbl_names:
display("Migrating Tablename ::" + tbl)
odo?
odo('{0}::{1}'.format(source, tbl), '{0}::{1}'.format(target, tbl))
migrate(url_config, url_config_bcp)
On running this on a SQLite database.
url_config = 'sqlite:///DB_CONFIG.db'
url_config_bcp = 'sqlite:///DB_CONFIG_BCP.db'
migrate(url_config, url_config_bcp)
I get following error.
How can I resolve this ?
OperationalError: (sqlite3.OperationalError) Could not decode to UTF-8 column 'V_WIMS_WELLS$COMMENTS' with text ' 29/12/2013 : SAP issue related , WO# was closed but still apprear in WIMS for confirmation .
WPH-41 Cut Surface CSG 9-5/8¿ on b
P.S :- I am aware that we can set textfactory expilicitly in sqlalchemy
db = create_engine('sqlite:///{0}'.format(sqlite_name))
conn = db.raw_connection()
conn.connection.text_factory = str
I don't know how to fix it within odo function
#A second attempt at writing function by passing metadata explicitly results in following error
def migrate(source, target, table_names=None):
src_db = Data(source)
engine = sa.create_engine(source)
conn = engine.raw_connection()
conn.connection.text_factory = str
metadata = sa.MetaData()
metadata.bind = engine
tbl_names = (src_db.fields, table_names)[table_names is not None]
for tbl in tbl_names:
display("Migrating Tablename ::" + tbl)
odo(sa.Table(tbl, metadata), '{0}::{1}'.format(target, tbl))
ERROR:-
---> 26 migrate(url_config, url_config_bcp)
<ipython-input-21-146061c63d09> in migrate(source, target, table_names)
21 for tbl in tbl_names:
22 display("Migrating Tablename ::" + tbl)
---> 23 odo(sa.Table(tbl, metadata), '{0}::{1}'.format(target, tbl))
25
C:\Anaconda\lib\site-packages\odo\odo.pyc in odo(source, target, **kwargs)
89 odo.append.append - Add things onto existing things
90 """
---> 91 return into(target, source, **kwargs)
C:\Anaconda\lib\site-packages\multipledispatch\dispatcher.pyc in __call__(self, *args, **kwargs)
162 self._cache[types] = func
163 try:
--> 164 return func(*args, **kwargs)
165
166 except MDNotImplementedError:
C:\Anaconda\lib\site-packages\odo\into.pyc in wrapped(*args, **kwargs)
41 raise TypeError('dshape argument is not an instance of DataShape')
42 kwargs['dshape'] = dshape
---> 43 return f(*args, **kwargs)
44 return wrapped
45
C:\Anaconda\lib\site-packages\odo\into.pyc in into_string(uri, b, dshape, **kwargs)
141
142 a = resource(uri, dshape=resource_ds, expected_dshape=dshape, **kwargs)
--> 143 return into(a, b, dshape=dshape, **kwargs)
144
145
C:\Anaconda\lib\site-packages\multipledispatch\dispatcher.pyc in __call__(self, *args, **kwargs)
162 self._cache[types] = func
163 try:
--> 164 return func(*args, **kwargs)
165
166 except MDNotImplementedError:
C:\Anaconda\lib\site-packages\odo\into.pyc in wrapped(*args, **kwargs)
41 raise TypeError('dshape argument is not an instance of DataShape')
42 kwargs['dshape'] = dshape
---> 43 return f(*args, **kwargs)
44 return wrapped
45
C:\Anaconda\lib\site-packages\odo\into.pyc in into_object(target, source, dshape, **kwargs)
129 if dshape is None:
130 dshape = discover(source)
--> 131 return append(target, source, dshape=dshape, **kwargs)
132
133
C:\Anaconda\lib\site-packages\multipledispatch\dispatcher.pyc in __call__(self, *args, **kwargs)
162 self._cache[types] = func
163 try:
--> 164 return func(*args, **kwargs)
165
166 except MDNotImplementedError:
C:\Anaconda\lib\site-packages\odo\backends\sql.pyc in append_table_to_sql_Table(t, o, **kwargs)
460 def append_table_to_sql_Table(t, o, **kwargs):
461 s = sa.select([o])
--> 462 return append(t, s, **kwargs)
463
464
C:\Anaconda\lib\site-packages\multipledispatch\dispatcher.pyc in __call__(self, *args, **kwargs)
162 self._cache[types] = func
163 try:
--> 164 return func(*args, **kwargs)
165
166 except MDNotImplementedError:
C:\Anaconda\lib\site-packages\odo\backends\sql.pyc in append_select_statement_to_sql_Table(t, o, bind, **kwargs)
470 return append(
471 t,
--> 472 convert(Iterator, o, bind=bind, **kwargs),
473 bind=bind,
474 **kwargs
C:\Anaconda\lib\site-packages\odo\core.pyc in __call__(self, *args, **kwargs)
42
43 def __call__(self, *args, **kwargs):
---> 44 return _transform(self.graph, *args, **kwargs)
45
46
C:\Anaconda\lib\site-packages\odo\core.pyc in _transform(graph, target, source, excluded_edges, ooc_types, **kwargs)
58 try:
59 for (A, B, f) in pth:
---> 60 x = f(x, excluded_edges=excluded_edges, **kwargs)
61 return x
62 except NotImplementedError as e:
C:\Anaconda\lib\site-packages\odo\backends\sql.pyc in select_or_selectable_to_frame(el, bind, **kwargs)
598 @convert.register(pd.DataFrame, (sa.sql.Select, sa.sql.Selectable), cost=200.0)
599 def select_or_selectable_to_frame(el, bind=None, **kwargs):
--> 600 columns, rows = batch(el, bind=bind)
601 row = next(rows, None)
602 if row is None:
C:\Anaconda\lib\site-packages\odo\backends\sql.pyc in batch(sel, chunksize, bind)
136 return
137 terator = rowterator(sel)
--> 138 return next(terator), concat(terator)
139
140
C:\Anaconda\lib\site-packages\odo\backends\sql.pyc in rowterator(sel, chunksize)
126 def rowterator(sel, chunksize=chunksize):
127 with getbind(sel, bind).connect() as conn:
--> 128 result = conn.execute(sel)
129 yield result.keys()
130
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
912 type(object))
913 else:
--> 914 return meth(self, multiparams, params)
915
916 def _execute_function(self, func, multiparams, params):
C:\Anaconda\lib\site-packages\sqlalchemy\sql\elements.pyc in _execute_on_connection(self, connection, multiparams, params)
321
322 def _execute_on_connection(self, connection, multiparams, params):
--> 323 return connection._execute_clauseelement(self, multiparams, params)
324
325 def unique_params(self, *optionaldict, **kwargs):
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_clauseelement(self, elem, multiparams, params)
1008 compiled_sql,
1009 distilled_params,
-> 1010 compiled_sql, distilled_params
1011 )
1012 if self._has_events or self.engine._has_events:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
1144 parameters,
1145 cursor,
-> 1146 context)
1147
1148 if self._has_events or self.engine._has_events:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1339 util.raise_from_cause(
1340 sqlalchemy_exception,
-> 1341 exc_info
1342 )
1343 else:
C:\Anaconda\lib\site-packages\sqlalchemy\util\compat.pyc in raise_from_cause(exception, exc_info)
198 exc_info = sys.exc_info()
199 exc_type, exc_value, exc_tb = exc_info
--> 200 reraise(type(exception), exception, tb=exc_tb)
201
202 if py3k:
C:\Anaconda\lib\site-packages\sqlalchemy\engine\base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
1137 statement,
1138 parameters,
-> 1139 context)
1140 except Exception as e:
1141 self._handle_dbapi_exception(
C:\Anaconda\lib\site-packages\sqlalchemy\engine\default.pyc in do_execute(self, cursor, statement, parameters, context)
448
449 def do_execute(self, cursor, statement, parameters, context=None):
--> 450 cursor.execute(statement, parameters)
451
452 def do_execute_no_params(self, cursor, statement, context=None):
OperationalError: (sqlite3.OperationalError) near "FROM": syntax error [SQL: u'SELECT \nFROM "C_PATTERN"']
You can pass
encodingargument while migrating inodo