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 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_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