Python MySQLdb.ProgrammingError() Examples

def getColumns(conn, connType, tableName, exampleNameForDB):
	retval = False
	if connType == DBTYPE_MYSQL:
		query = "SHOW COLUMNS FROM {}".format(tableName)
	else:
		query = "PRAGMA table_info({})".format(tableName)
	try:
		cursor = conn.cursor()
		cursor.execute(query)
		retval = cursor.fetchall()
		cursor.close()
	except (MySQLdb.ProgrammingError, sqlite3.Error,) as e:
		print "TABLE {} not found for example {}: {}".format(tableName,exampleNameForDB, e.args)
	if connType == DBTYPE_SQLITE:
		retval = [(x[1],) for x in retval] # weird unSELECTable tuple retval
	return retval 

def apply_migration(self, name, statements, fake=False):
        import psycopg2
        connection = self.get_connection()
        with connection.cursor() as cursor:
            try:
                if not fake and statements.strip():
                    cursor.execute(statements)
            except (
                    psycopg2.OperationalError,
                    psycopg2.ProgrammingError) as e:
                connection.rollback()
                print(e.message)
                from sqlibrist.helpers import ApplyMigrationFailed

                raise ApplyMigrationFailed
            else:
                cursor.execute('INSERT INTO sqlibrist.migrations '
                               '(migration) VALUES (%s);',
                               [name.split('/')[-1]])
                connection.commit() 

def unapply_migration(self, name, statements, fake=False):
        import psycopg2
        connection = self.get_connection()
        with connection.cursor() as cursor:
            try:
                if not fake:
                    cursor.execute(statements)
            except (
                    psycopg2.OperationalError,
                    psycopg2.ProgrammingError) as e:
                connection.rollback()
                print(e.message)
                from sqlibrist.helpers import ApplyMigrationFailed

                raise ApplyMigrationFailed
            else:
                cursor.execute('DELETE FROM sqlibrist.migrations '
                               'WHERE migration = (%s); ', [name])
                connection.commit() 

def apply_migration(self, name, statements, fake=False):
        import MySQLdb
        connection = self.get_connection()
        cursor = connection.cursor()

        try:
            if not fake and statements.strip():
                cursor.execute(statements)
        except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as e:
            print('\n'.join(map(str, e.args)))
            from sqlibrist.helpers import ApplyMigrationFailed

            raise ApplyMigrationFailed
        else:
            cursor.execute('INSERT INTO sqlibrist_migrations '
                           '(migration) VALUES (%s);',
                           [name.split('/')[-1]]) 

def unapply_migration(self, name, statements, fake=False):
        import MySQLdb
        connection = self.get_connection()
        cursor = connection.cursor()

        try:
            if not fake:
                cursor.execute(statements)
        except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as e:
            print('\n'.join(map(str, e.args)))
            from sqlibrist.helpers import ApplyMigrationFailed

            raise ApplyMigrationFailed
        else:
            cursor.execute('DELETE FROM sqlibrist_migrations '
                           'WHERE migration = (%s); ', [name]) 

def __execute_query(self, sql):
		with warnings.catch_warnings():
			warnings.simplefilter('error', MySQLdb.Warning)
			try:
				self.__cur.execute(sql)
				return True
			except MySQLdb.Error, e:
				print "An Error occured running query. %s" %e
				#print sql;
				return False
			except MySQLdb.Warning, e:
				print "An Warning occured running query. %s" %e
				return True
			except MySQLdb.ProgrammingError, e:
				print "A ProgrammingError occured running query. %s" %e
				exit(1)
				return False 

def execute_sql_with_fetch(self, resource_name, sql, values):
        """Executes a provided sql statement with fetch.

        Args:
            resource_name (str): String of the resource name.
            sql (str): String of the sql statement.
            values (tuple): Tuple of string for sql placeholder values.

        Returns:
            list: A list of dict representing rows of sql query result.

        Raises:
            MySQLError: When an error has occured while executing the query.
        """
        try:
            cursor = self.conn.cursor(cursorclass=cursors.DictCursor)
            cursor.execute(sql, values)
            return cursor.fetchall()
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            raise MySQLError(resource_name, e) 

def execute_sql_with_commit(self, resource_name, sql, values):
        """Executes a provided sql statement with commit.

        Args:
            resource_name (str): String of the resource name.
            sql (str): String of the sql statement.
            values (tuple): Tuple of string for sql placeholder values.

        Raises:
            MySQLError: When an error has occured while executing the query.
        """
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, values)
            self.conn.commit()
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            raise MySQLError(resource_name, e) 

def run_ddl(self, db, sql):
        log.debug("Creating table using: \n {}".format(sql))
        try:
            self.use_db(db)
            self.execute_sql(sql)
        except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as e:
            errnum, errmsg = e.args
            log.error(
                "SQL execution error: [{}] {}\n"
                "When executing: {}\n"
                "With args: {}"
                .format(errnum, errmsg, self._sql_now, self._sql_args_now))
            raise OSCError('GENERIC_MYSQL_ERROR',
                           {'stage': "running DDL on db '{}'".format(db),
                            'errnum': errnum,
                            'errmsg': errmsg},
                           mysql_err_code=errnum) 

def exec_sql(self, sql):
        conn = self._get_conn()
        try:
            with conn as cur:
                cur.execute(sql)
                return cur.fetchall()
        except MySQLdb.ProgrammingError as e:
            LOG.error("execute sql ({0}) error {1}".format(sql, e))
            raise e
        except MySQLdb.OperationalError as e:
            # create connection if connection has interrupted
            conn = self._create_new_conn()
            raise e
        finally:
            self._put_conn(conn) 

def exec_sql(self, sql):
        conn = self._get_conn()
        try:
            with conn as cur:
                cur.execute(sql)
                return cur.fetchall()
        except MySQLdb.ProgrammingError as e:
            LOG.error("execute sql ({0}) error {1}".format(sql, e))
            raise e
        except MySQLdb.OperationalError as e:
            # create connection if connection has interrupted
            conn = self._create_new_conn()
            raise e
        finally:
            self._put_conn(conn) 

def do_query(self,sql,cur,conn):
		rows_affected 	= None
		e 				= None
		row 			= None
		w 				= None
		with warnings.catch_warnings(record=True) as w:
			try:
	  			rows_affected = cur.execute(sql)
	  			row = self.__cur.fetchone()
			except MySQLdb.Error, e:
				#self.statistics.processError(e)
				#print self.name + " - An Error occured running query. %s" %e
				#print sql
				#print "----------------------------"
				if e[1] == 'MySQL server has gone away':
					#print self.name + " - An Error occured running query. %s" %e
					pass
				#print sql;
				#conn.send((rows_affected,e,w,row))
			except MySQLdb.ProgrammingError, e:
				print self.name + " - A ProgrammingError occured running query. %s" %e
				#print sql;
				#print "----------------end----------------"
		
		if conn != None:
			conn.send((rows_affected,e,w,row))
		else:
			return (rows_affected,e,w,row) 

def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 

def get_buckets_acls(self, resource_name, timestamp):
        """Select the bucket acls from a bucket acls snapshot table.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
                YYYYMMDDTHHMMSSZ.

        Returns:
            list: List of bucket acls.

        Raises:
            MySQLError: An error with MySQL has occurred.
        """
        bucket_acls = {}
        cnt = 0
        try:
            bucket_acls_sql = select_data.BUCKET_ACLS.format(timestamp)
            rows = self.execute_sql_with_fetch(resource_name,
                                               bucket_acls_sql,
                                               None)
            for row in rows:
                bucket_acl = bkt_acls.\
                BucketAccessControls(bucket=row['bucket'],
                                     entity=row['entity'],
                                     email=row['email'],
                                     domain=row['domain'],
                                     role=row['role'],
                                     project_number=row['project_number'])
                bucket_acls[cnt] = bucket_acl
                cnt += 1
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            LOGGER.error(errors.MySQLError(resource_name, e))
        return bucket_acls 

def get_bigquery_acls(self, resource_name, timestamp):
        """Select the Big Query acls from a Big Query acls snapshot table.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
            YYYYMMDDTHHMMSSZ.

        Returns:
            dict: Dictionary keyed by the count of ACLs and then the ACLs.

        Raises:
            MySQLError: An error with MySQL has occurred.
        """
        bigquery_acls = {}
        cnt = 0
        try:
            bigquery_acls_sql = select_data.BIGQUERY_ACLS.format(timestamp)
            rows = self.execute_sql_with_fetch(resource_name,
                                               bigquery_acls_sql,
                                               None)
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            LOGGER.error(errors.MySQLError(resource_name, e))

        for row in rows:
            bigquery_acl = bq_acls.BigqueryAccessControls(
                dataset_id=row['dataset_id'],
                special_group=row['access_special_group'],
                user_email=row['access_user_by_email'],
                domain=row['access_domain'],
                role=row['role'],
                group_email=row['access_group_by_email'],
                project_id=row['project_id'])
            bigquery_acls[cnt] = bigquery_acl
            cnt += 1

        return bigquery_acls 

def _get_cloudsql_instance_acl_map(self, resource_name, timestamp):
        """Create CloudSQL instance acl map.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
                YYYYMMDDTHHMMSSZ.

        Returns:
            dict: Map of instance acls.

        Raises:
            MySQLError: An error with MySQL has occurred.
        """
        cloudsql_acls_map = {}
        try:
            cloudsql_acls_sql = select_data.CLOUDSQL_ACLS.format(timestamp)
            rows = self.execute_sql_with_fetch(resource_name,
                                               cloudsql_acls_sql,
                                               None)
            for row in rows:
                acl_list = []
                project_number = row['project_number']
                instance_name = row['instance_name']
                network = row['value']
                hash_key = hash(str(project_number) + ',' + instance_name)
                if hash_key in cloudsql_acls_map:
                    if network not in cloudsql_acls_map[hash_key]:
                        cloudsql_acls_map[hash_key].append(network)
                else:
                    acl_list.append(network)
                    cloudsql_acls_map[hash_key] = acl_list
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            LOGGER.error(errors.MySQLError(resource_name, e))

        return cloudsql_acls_map 

def load_data(self, resource_name, timestamp, data):
        """Load data into a snapshot table.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
                YYYYMMDDTHHMMSSZ.
            data (iterable): An iterable or a list of data to be uploaded.

        Raises:
            MySQLError: When an error has occured while executing the query.
        """
        with csv_writer.write_csv(resource_name, data) as csv_file:
            try:
                snapshot_table_name = self._create_snapshot_table_name(
                    resource_name, timestamp)
                load_data_sql = load_data_sql_provider.provide_load_data_sql(
                    resource_name, csv_file.name, snapshot_table_name)
                LOGGER.debug('SQL: %s', load_data_sql)
                cursor = self.conn.cursor()
                cursor.execute(load_data_sql)
                self.conn.commit()
                # TODO: Return the snapshot table name so that it can be tracked
                # in the main snapshot table.
            except (DataError, IntegrityError, InternalError,
                    NotSupportedError, OperationalError,
                    ProgrammingError) as e:
                raise MySQLError(resource_name, e) 

def select_group_ids(self, resource_name, timestamp):
        """Select the group ids from a snapshot table.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
                YYYYMMDDTHHMMSSZ.

        Returns:
            list: A list of group ids.

        Raises:
            MySQLError: When an error has occured while executing the query.
        """
        try:
            group_ids_sql = select_data.GROUP_IDS.format(timestamp)
            cursor = self.conn.cursor(cursorclass=cursors.DictCursor)
            cursor.execute(group_ids_sql)
            rows = cursor.fetchall()
            return [row['group_id'] for row in rows]
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            raise MySQLError(resource_name, e) 

def get_latest_snapshot_timestamp(self, statuses):
        """Select the latest timestamp of the completed snapshot.

        Args:
            statuses (tuple): The tuple of snapshot statuses to filter on.

        Returns:
            str: The string timestamp of the latest complete snapshot.

        Raises:
            MySQLError: When no rows are found.
        """
        # Build a dynamic parameterized query string for filtering the
        # snapshot statuses
        if not isinstance(statuses, tuple):
            statuses = ('SUCCESS',)

        status_params = ','.join(['%s']*len(statuses))
        filter_clause = SNAPSHOT_STATUS_FILTER_CLAUSE.format(status_params)
        try:
            cursor = self.conn.cursor()
            cursor.execute(
                select_data.LATEST_SNAPSHOT_TIMESTAMP + filter_clause, statuses)
            row = cursor.fetchone()
            if row:
                return row[0]
            raise NoResultsError('No snapshot cycle found.')
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError, NoResultsError) as e:
            raise MySQLError('snapshot_cycles', e) 

def execute(self, *args, **kwargs):
        try:
            result = self._cursor.execute(*args, **kwargs)
        except MySQLdb.ProgrammingError as e:
            raise MySQLdb.ProgrammingError(e.args[0], e.args[1] + '.\nSTATEMENT: {}'.format(self._cursor._last_executed))
        except MySQLdb.OperationalError as e:
            # Sometimes a MySQL session times out. In this case, we wish to reconnect, and reissue the query.
            if e[0] == 2006:
                self.connect()
                result = self._cursor.execute(*args, **kwargs)
            else:
                raise MySQLdb.OperationalError(e.args[0], e.args[1] + '.\nSTATEMENT: {}'.format(self._cursor._last_executed))
        return result 

def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 

def execute(self,qry):
        if self.db:
            try: res=self.db.execute(qry)
            except MySQLdb.OperationalError as message:
                log.error("Error %d:\n%s" % (message[ 0 ], message[ 1 ] ))
                return 0

            except MySQLdb.ProgrammingError as message:
                log.error("Error %d:\n%s" % (message[ 0 ], message[ 1 ] ))
                return 0

            else:
                log.debug('Query Returned '+str(res)+' results')
                return self.db.fetchall() 

def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 

def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) FROM entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 

def show_create_table(instance, db, table, standardize=True):
    """ Get a standardized CREATE TABLE statement

    Args:
    instance - a hostAddr object
    db - the MySQL database to run against
    table - the table on the db database to run against
    standardize - Remove AUTO_INCREMENT=$NUM and similar

    Returns:
    A string of the CREATE TABLE statement
    """
    conn = connect_mysql(instance)
    cursor = conn.cursor()

    try:
        cursor.execute('SHOW CREATE TABLE `{db}`.`{table}`'.format(
            table=table, db=db))
        ret = cursor.fetchone()['Create Table']
        if standardize is True:
            ret = re.sub('AUTO_INCREMENT=[0-9]+ ', '', ret)
    except MySQLdb.ProgrammingError as detail:
        (error_code, msg) = detail.args
        if error_code != MYSQL_ERROR_NO_SUCH_TABLE:
            raise
        ret = ''

    return ret 

def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 

def get_cloudsql_acls(self, resource_name, timestamp):
        """Select the cloudsql acls for project from a snapshot table.

        Args:
            resource_name (str): String of the resource name.
            timestamp (str): String of timestamp, formatted as
                YYYYMMDDTHHMMSSZ.

        Returns:
            list: List of cloudsql acls.

        Raises:
            MySQLError: An error with MySQL has occurred.
        """
        cloudsql_acls = {}
        cnt = 0
        try:
            cloudsql_instances_sql = (
                select_data.CLOUDSQL_INSTANCES.format(timestamp))
            rows = self.execute_sql_with_fetch(resource_name,
                                               cloudsql_instances_sql,
                                               None)
            acl_map = self._get_cloudsql_instance_acl_map(resource_name,
                                                          timestamp)

            for row in rows:
                project_number = row['project_number']
                instance_name = row['name']
                ssl_enabled = row['settings_ip_configuration_require_ssl']
                authorized_networks = self.\
                _get_networks_for_instance(acl_map,
                                           project_number,
                                           instance_name)

                cloudsql_acl = csql_acls.\
                CloudSqlAccessControl(instance_name=instance_name,
                                      authorized_networks=authorized_networks,
                                      ssl_enabled=ssl_enabled,
                                      project_number=project_number)
                cloudsql_acls[cnt] = cloudsql_acl
                cnt += 1
        except (DataError, IntegrityError, InternalError, NotSupportedError,
                OperationalError, ProgrammingError) as e:
            LOGGER.error(errors.MySQLError(resource_name, e))
        return cloudsql_acls 

def _queue_submitting(self):
        log.info("MysqlFastInsert thread:{} start".format(threading.current_thread()))
        
        conn, cur = self._get_connection()
        
        while True:
            try:
                lines = self.queue.get()
            except:
                log.error("mysql-inserter unable to get queue", exc_info=True)
                time.sleep(6)
                continue
            
            # log.debug("line:", len(lines), lines[:3])
            start_time = time.time()
            try:
                row_count = self.insert_function(cur, lines)
                # row_count = len(lines)
            except MySQLdb.ProgrammingError:
                # ??: ????, ?????
                log.error(
                    "mysql???? MySQLdb.ProgrammingError! process:{} cursor:{}".format(
                        self._multiprocessing.current_process(),
                        cur),
                    exc_info=True)
                # ?????
            
            except:
                log.error(
                    "mysql insert error! process:{} cursor:{}".format(
                        self._multiprocessing.current_process(),
                        cur),
                    exc_info=True)
                
                conn, cur = self.re_connect(conn)
            
            else:
                try:
                    conn.commit()
                except:
                    log.error("commit error!", exc_info=True)
                    
                    conn, cur = self.re_connect(conn)
                else:
                    log.debug("mysql successfully inserted: {} rows in {}ms".format(
                        row_count, round((time.time() - start_time) * 1000, 2)))
                    
                    self.count.value += row_count  # ???????
            
            finally:
                self.queue.task_done()