def drop_table(dbo, tablename, schema=None, commit=True):
"""
Drop a database table.
Keyword arguments:
dbo : database object
DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or
SQLAlchemy connection, engine or session
tablename : text
Name of the table
schema : text
Name of the database schema the table is in
commit : bool
If True commit the changes
"""
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + "." + tablename
sql = u"DROP TABLE %s" % tablename
_execute(sql, dbo, commit)
def drop_table(dbo, tablename, schema=None, commit=True):
"""
Drop a database table.
Keyword arguments:
dbo : database object
DB-API 2.0 connection, callable returning a DB-API 2.0 cursor, or
SQLAlchemy connection, engine or session
tablename : text
Name of the table
schema : text
Name of the database schema the table is in
commit : bool
If True commit the changes
"""
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
sql = u'DROP TABLE %s' % tablename
_execute(sql, dbo, commit)
def _todb_dbapi_connection(table,
connection,
tablename,
schema=None,
commit=True,
truncate=False,
replace=False):
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
# determine paramstyle and build placeholders string
placeholders = _placeholders(connection, colnames)
debug('placeholders: %r', placeholders)
# get a cursor
cursor = connection.cursor()
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
cursor.execute(truncatequery)
# just in case, close and resurrect cursor
cursor.close()
cursor = connection.cursor()
insertcolnames = ', '.join(colnames)
if replace:
insertquery = SQL_REPLACE_QUERY % (tablename, insertcolnames,
placeholders)
else:
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames,
placeholders)
debug('insert data via query %r' % insertquery)
cursor.executemany(insertquery, it)
# finish up
debug('close the cursor')
cursor.close()
if commit:
debug('commit transaction')
connection.commit()
def _todb_sqlalchemy_connection(table,
connection,
tablename,
schema=None,
commit=True,
truncate=False):
debug('connection: %r', connection)
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
# N.B., we need to obtain a reference to the underlying DB-API connection so
# we can import the module and determine the paramstyle
proxied_raw_connection = connection.connection
actual_raw_connection = proxied_raw_connection.connection
# determine paramstyle and build placeholders string
placeholders = _placeholders(actual_raw_connection, colnames)
debug('placeholders: %r', placeholders)
if commit:
debug('begin transaction')
trans = connection.begin()
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
connection.execute(truncatequery)
insertcolnames = ', '.join(colnames)
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
debug('insert data via query %r' % insertquery)
for row in it:
connection.execute(insertquery, row)
# finish up
if commit:
debug('commit transaction')
trans.commit()
def _todb_dbapi_mkcurs(table,
mkcurs,
tablename,
schema=None,
commit=True,
truncate=False):
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
debug('obtain cursor and connection')
cursor = mkcurs()
# N.B., we depend on this optional DB-API 2.0 attribute being implemented
assert hasattr(cursor, 'connection'), \
'could not obtain connection via cursor'
connection = cursor.connection
# determine paramstyle and build placeholders string
placeholders = _placeholders(connection, colnames)
debug('placeholders: %r', placeholders)
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
cursor.execute(truncatequery)
# N.B., may be server-side cursor, need to resurrect
cursor.close()
cursor = mkcurs()
insertcolnames = ', '.join(colnames)
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
debug('insert data via query %r' % insertquery)
cursor.executemany(insertquery, it)
cursor.close()
if commit:
debug('commit transaction')
connection.commit()
def _todb_sqlalchemy_connection(table, connection, tablename, schema=None,
commit=True, truncate=False):
debug('connection: %r', connection)
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
# N.B., we need to obtain a reference to the underlying DB-API connection so
# we can import the module and determine the paramstyle
proxied_raw_connection = connection.connection
actual_raw_connection = proxied_raw_connection.connection
# determine paramstyle and build placeholders string
placeholders = _placeholders(actual_raw_connection, colnames)
debug('placeholders: %r', placeholders)
if commit:
debug('begin transaction')
trans = connection.begin()
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
connection.execute(truncatequery)
insertcolnames = ', '.join(colnames)
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
debug('insert data via query %r' % insertquery)
for row in it:
connection.execute(insertquery, row)
# finish up
if commit:
debug('commit transaction')
trans.commit()
def stmt(self):
# handle fields
fields = self.fields
if fields is None:
# default to non geom fields
fields = self.table.non_geom_fields
fields = [_quote(field) for field in fields]
# handle geom
geom_field = self.table.geom_field
if geom_field and self.return_geom:
wkt_getter = self.table.wkt_getter(geom_field, self.to_srid)
fields.append(wkt_getter)
# form statement
fields_joined = ', '.join(fields)
stmt = 'SELECT {} FROM {}'.format(fields_joined,
self.table.name_with_schema)
where = self.where
if where:
stmt += ' WHERE {}'.format(where)
limit = self.limit
if limit:
stmt += ' LIMIT {}'.format(limit)
# print('from stmt', stmt)
return stmt
def start(self):
BaseManager.register('getDispatchedJobQueue')
BaseManager.register('getConfigQueue')
self.manager = BaseManager(address=(self.host, self.port), authkey=b'huafeng@123+1s')
try:
self.manager.connect()
except:
time.sleep(3)
self.manager.connect()
self.dispatchJob = self.manager.getDispatchedJobQueue()
self.setting = self.manager.getConfigQueue().get(1)
self.manager.getConfigQueue().put(self.setting)
TO_DB_CON = getDBConnection(self.setting['to_db_setting'])
hdr = self.setting['to_field_list']
flds = list(map(str, hdr))
colnames = [_quote(n) for n in flds]
insertcolnames = ', '.join(colnames)
placeholders = _placeholders(TO_DB_CON, colnames)
insertquery = SQL_INSERT_QUERY % (self.setting['to_db_table'], insertcolnames, placeholders)
TO_DB_CON.close()
#FROM_DB_CON.close()
return insertquery
def _todb_dbapi_mkcurs(table, mkcurs, tablename, schema=None, commit=True,
truncate=False):
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
debug('obtain cursor and connection')
cursor = mkcurs()
# N.B., we depend on this optional DB-API 2.0 attribute being implemented
assert hasattr(cursor, 'connection'), \
'could not obtain connection via cursor'
connection = cursor.connection
# determine paramstyle and build placeholders string
placeholders = _placeholders(connection, colnames)
debug('placeholders: %r', placeholders)
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
cursor.execute(truncatequery)
# N.B., may be server-side cursor, need to resurrect
cursor.close()
cursor = mkcurs()
insertcolnames = ', '.join(colnames)
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
debug('insert data via query %r' % insertquery)
cursor.executemany(insertquery, it)
cursor.close()
if commit:
debug('commit transaction')
connection.commit()
def _todb_dbapi_connection(table, connection, tablename, schema=None,
commit=True, truncate=False):
# sanitise table name
tablename = _quote(tablename)
if schema is not None:
tablename = _quote(schema) + '.' + tablename
debug('tablename: %r', tablename)
# sanitise field names
it = iter(table)
hdr = next(it)
flds = list(map(text_type, hdr))
colnames = [_quote(n) for n in flds]
debug('column names: %r', colnames)
# determine paramstyle and build placeholders string
placeholders = _placeholders(connection, colnames)
debug('placeholders: %r', placeholders)
# get a cursor
cursor = connection.cursor()
if truncate:
# TRUNCATE is not supported in some databases and causing locks with
# MySQL used via SQLAlchemy, fall back to DELETE FROM for now
truncatequery = SQL_TRUNCATE_QUERY % tablename
debug('truncate the table via query %r', truncatequery)
cursor.execute(truncatequery)
# just in case, close and resurrect cursor
cursor.close()
cursor = connection.cursor()
insertcolnames = ', '.join(colnames)
insertquery = SQL_INSERT_QUERY % (tablename, insertcolnames, placeholders)
debug('insert data via query %r' % insertquery)
cursor.executemany(insertquery, it)
# finish up
debug('close the cursor')
cursor.close()
if commit:
debug('commit transaction')
connection.commit()
def name_with_schema(self):
"""Returns the table name prepended with the schema name, prepared for
a query."""
if self.schema:
comps = [self.schema, self.name]
name_with_schema = '.'.join([_quote(x) for x in comps])
else:
name_with_schema = self.name
return name_with_schema
def start(self):
# print('slave start')
BaseManager.register('getDispatchedJobQueue')
BaseManager.register('getConfigQueue')
self.manager = BaseManager(address=(self.host, self.port),
authkey=b'huafeng@123+1s')
try:
self.manager.connect()
except:
time.sleep(3)
self.manager.connect()
self.dispatchJob = self.manager.getDispatchedJobQueue()
self.setting = self.manager.getConfigQueue().get(1)
self.manager.getConfigQueue().put(self.setting)
TO_DB_CON = getDBConnection(self.setting['to_db_setting'])
FROM_DB_CON = getDBConnection(self.setting['from_db_setting'])
fromTable = petl.fromdb(FROM_DB_CON, self.setting['line'])
it = iter(fromTable)
hdr = next(it)
flds = list(map(str, hdr))
to_colnames = []
for n in flds:
if len(n.encode('utf-8')) > 30:
to_colnames.append(_quote(n[0:10]))
else:
to_colnames.append(_quote(n))
insertcolnames = ', '.join(to_colnames)
placeholders = _placeholders(TO_DB_CON, to_colnames)
insertquery = SQL_INSERT_QUERY % (self.setting['to_db_table'],
insertcolnames, placeholders)
TO_DB_CON.close()
#FROM_DB_CON.close()
return insertquery
